change this array formula with an extra "IF" condition

keithmct

Active Member
Joined
Mar 9, 2007
Messages
254
Office Version
  1. 2021
Platform
  1. Windows

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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hard to follow...

1. Retrieval range = Categories!$K$7:$K$1402

2. Categories!$H$7:$H$1402 must equal E14. What is the value of E14?

3. Categories!$I$7:$I$1402 must equal S14. What is the value of S14?

4. What is the range that must contain or must start with newyork?
 
Upvote 0
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
1400
PVCvenetiannewyork50
3​
1402
PVCvenetiannewyork63
63​
Sheet: Categories

not sure if this helps:
i want to have the end result (AE14) of the lookup for PVCvenetian and newyork (S14) + blade size 50 (Q14) to equal 3, and
i want to have the end result (AE15) of the lookup for PVCvenetian and newyork (S15) + blade size 63 (Q15) to equal 63
but I dont want to have newyork50 or newyork63 in column S, I just want to have newyork
at other times blind type will be something other than PVCvenetian, but then I wont need S & Q concatenated in the formula, hence the "IF" statement.
 
Last edited:
Upvote 0
E14 = PVCvenetian
S14 = newyork
and I don't understand question 4, maybe you could rephrase
 
Upvote 0
E14 = PVCvenetian
S14 = newyork
and I don't understand question 4, maybe you could rephrase

By your answer above, 4 no longer necessary.

Control+shift+enter, not just enter:

=IF(E14="","",INDEX(Categories!$K$7:$K$1402,MATCH(1,IF(Categories!$H$7:$H$1402=E14,IF(ISNUMBER(SEARCH("|"&S14,"|"&Categories!$I$7:$I$1402)),1)),0)))
 
Upvote 0
I copied that formula down and it worked for newyork50 (result = 3) but newyork63 did not work (result 3 not 63)
what does the ISNUMBER(SEARCH..... bit do?
 
Upvote 0
I copied that formula down and it worked for newyork50 (result = 3) but newyork63 did not work (result 3 not 63)
what does the ISNUMBER(SEARCH..... bit do?

That implements your "I dont want to have newyork50 or newyork63 in column S, I just want to have newyork".

You might want to post a relevant input and the desired output that goes with that input.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top