Excel: change this array formula with an extra "IF" condition


Excel 2016 (Windows) 32 bit

CEGIKMOQSUWYAAACAEAFAG
13windowblind typewidthdropbracketscontrolsbunchblade sizeFabricColourtrack colourrolloperationstyle no.catbase cost
14PVCvenetian1800120050newyork503279.40
15PVCvenetian1800120063newyork6363319.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


This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of August 11, 2017.


For more resources for Microsoft Excel