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