change this array formula with an extra "IF" condition

[keithmct]

Excel 2016 (Windows) 32 bit
	C	E	G	I	K	M	O	Q	S	U	W	Y	AA	AC	AE	AF	AG
13	window	blind type	width	drop	brackets	controls	bunch	blade size	Fabric	Colour	track colour	roll	operation	style no.	cat		base cost
14		PVCvenetian	1800	1200				50	newyork50						3		279.40
15		PVCvenetian	1800	1200				63	newyork63						63		319.00
Home

I have this array formula in AE3:
=IF(E14="","",INDEX(Categories!$H$7:$K$1402,MATCH(1,(Categories!$H$7:$H$1402=E14)*(Categories!$I$7:$I$1402=S14),0),4))
that matches the type of blind (PVCvenetian) with a category, and goes to the price grids I have set up to get a price based on the width and height, or go to the next highest price if it cant find an exact match.
What I would like to amend in the above example, where it has newyork50 and newyork63, is to only have "newyork" but put the IF statement in there somewhere to say: IF the blind type says PVCvenetian, then look up the price grid called newyork50 by concatenating S14 + Q14.
My 4 column table on the Categories page at H7:K1402 would say:
PVCvenetian newyork blank column 3
PVCvenetian newyork blank column 63

any suggestions much appreciated

 

[Aladin Akyurek]

I want it to look at newyork in S14 AND look in Q14 for either 50 or 63. If it sees 50, then get the result (category) 3 in column 4 of the table, but if it sees newyork AND 63 then it returns category 63 in column 4 of the table.

=IF(E14="","",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E14,IF(Categories!$I$7:$I$1402=S14&Q14,1)),0)))

 

[keithmct]

nearly there.
that only works if E14 = PVCvenetian. As mentioned before, sometimes the blind type in E14 is Vertical or Holland or Roman or Panelglide or curtain.
If these other options are chosen, I don't want the S14&Q14 bit, I just want it to have S14, therefore I need the part "if E14 = PVCvenetian, then concatenate S14&Q14, but if not then only have S14"

 

[keithmct]

I have come up with this but it is really long
=IF(E16="PVCvenetian",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E16,IF(Categories!$I$7:$I$1402=S16&Q16,1)),0)),INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E16,IF(Categories!$I$7:$I$1402=S16,1)),0)))
any way to shorten it?

 

[Aladin Akyurek]

nearly there.
that only works if E14 = PVCvenetian. As mentioned before, sometimes the blind type in E14 is Vertical or Holland or Roman or Panelglide or curtain.
If these other options are chosen, I don't want the S14&Q14 bit, I just want it to have S14, therefore I need the part "if E14 = PVCvenetian, then concatenate S14&Q14, but if not then only have S14"

Either...

=IF(E14="","",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E14,IF(Categories!$I$7:$I$1402=IF(E14="PVCvenetian",S14&Q14,S14),1)),0)))

Or...

=IF(E14="","",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E14,IF(ISNUMBER(SEARCH(E14="PVCvenetian",S14&Q14,S14),Categories!$I$7:$I$1402)),1)),0)))

​

 

[keithmct]

the first one works perfectly, so I'm going with that. thank you for all your efforts. A+
For your information, the second one doesn't work. It says "you have entered too many arguments" and highlights the $I$1402 part.

 

[Aladin Akyurek]

the first one works perfectly, so I'm going with that. thank you for all your efforts. A+
For your information, the second one doesn't work. It says "you have entered too many arguments" and highlights the $I$1402 part.

Second edited, control+shift+enter:

=IF(E14="","",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E14,IF(ISNUMBER(SEARCH(IF(E14="PVCvenetian",S14&Q14,S14),Categories!$I$7:$I$1402)),1)),0)))