IF Syntax in MATCH INDEX Formula?

[agerrard]

Hi All,

Im using MS Excel 2007.

Just have a question about the correct syntax in a formula with MATCH INDEX. I'm sure its simple but i keep getting stuck.

My example is below:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
2							Discount or Offer	Discount or Offer	Suggested Promo Sell $	Suggested Promo Sell $
3							29/06/2015	6/07/2015	29/06/2015	6/07/2015
4	Ref	Description	Sub Category	RRP	Cost	Margin
5	77216ea	Product1	CategoryA	$13.47	$4.53	66.4%	-20%	-20%	$ 4.00	$ 4.00
6	226801ea	Product2	CategoryA	$19.99	$7.43	62.8%	-22%		$ 4.00	$ 4.00
FY 2016

Cell Formulas
Range	Formula
H3	=G3+7
H5	=IF(INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B5,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(H$3,'PROMO DISCOUNT'!$E$3:$H$3,0))<>0,INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B5,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(H$3,'PROMO DISCOUNT'!$E$3:$H$3,0)),"")
H6	=IF(INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B6,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(H$3,'PROMO DISCOUNT'!$E$3:$H$3,0))<>0,INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B6,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(H$3,'PROMO DISCOUNT'!$E$3:$H$3,0)),"")
I3	=G3
I5	=IF(INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B5,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(I$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0))<>0,INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B5,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(I$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0)),"")
I6	=IF(INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B6,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(I$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0))<>0,INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B6,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(I$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0)),"")
J3	=H3
J5	=IF(INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B5,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(J$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0))<>0,INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B5,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(J$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0)),"")
J6	=IF(INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B6,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(J$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0))<>0,INDEX('PROMO PRICE_RETRIEVAL'!$E$4:$H$5,MATCH($B6,'PROMO PRICE_RETRIEVAL'!$D$4:$D$5,0),MATCH(J$3,'PROMO PRICE_RETRIEVAL'!$E$3:$H$3,0)),"")
G5	=IF(INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B5,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(G$3,'PROMO DISCOUNT'!$E$3:$H$3,0))<>0,INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B5,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(G$3,'PROMO DISCOUNT'!$E$3:$H$3,0)),"")
G6	=IF(INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B6,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(G$3,'PROMO DISCOUNT'!$E$3:$H$3,0))<>0,INDEX('PROMO DISCOUNT'!$E$4:$H$5,MATCH($B6,'PROMO DISCOUNT'!$D$4:$D$5,0),MATCH(G$3,'PROMO DISCOUNT'!$E$3:$H$3,0)),"")

What i'm trying to fix is in cells I5, I6, J5 and J6 (in the above table). So say for example if you look at cell J6. I want excel to look in cell H6 and if the value in H6 is a blank then i want the formula in J6 to also return a blank. Otherwise i need it to return the value that corresponds to the date and product description in the below data table:

Excel 2007
	C	D	E	F	G	H
3			29/06/2015	6/07/2015	13/07/2015	20/07/2015
4	1215BP4	Product1	4.00	4.00	4.00	4.00
5	1212BP4	Product2	4.00	4.00	4.00	4.00
PROMO PRICE_RETRIEVAL

Cell Formulas
Range	Formula
E3	42184
E4	4
E5	4
F3	42191
F4	4
F5	4
G3	42198
G4	4
G5	4
H3	42205
H4	4
H5	4
C4	1215BP4
C5	1212BP4
D4	Product1
D5	Product2

Hopefully this makes sense!

 

[DILIPandey]

It worked in my sheet using below formula:-

In Cell J6:-

=IF(H6="","",INDEX('PROMO PRICE_RETRIEVAL'!$A$1:$H$5,MATCH('FY 2016'!$C6,'PROMO PRICE_RETRIEVAL'!$D$1:$D$5,0),MATCH('FY 2016'!J$3,'PROMO PRICE_RETRIEVAL'!$A$3:$Z$3,0)))


Regards,
DILIPandey

 

[agerrard]

Hi DILIPandey,

Thanks so much for your help. Everything worked! Cheers

 

[DILIPandey]

you are welcome agerrard.

Cheers

Regards,
DILIPandey