Vlookup based on 4 variables

[jamphan]

I have a 4 column worksheet with number ranges in each column then a 5th column with a letter assignment so that if my 4 numbers fall in the range the letter will be returned. For example:

A B C D Grade
100 500 150 600 A
110 550 175 610 B
120 600 200 620 C

So if my numbers are A =107; B=545; C= 162;D= 601 then my grade will be a B since all the numbers are <= to the numbers in that row. It gets a bit more complicated than that as there are over 20,000 combinations but that is the jist of it. Any solutions?

[Andrew-R]

I put your four values in cells F1,G1,H1 and I1 and then used this formula:

=INDEX(E:E,MAX(INDEX(ROW(A1:A3)*(A1:A3<=F1)*(A2:A4>F1)*(B1:B3<=G1)*(B2:B4>G1)*(C1:C3<=H1)*(C2:C4>H1)*(D1:D3<=I1)*(D2:D4>I1),0)))

Is that what you're looking for?

Edited to add: Forgot to mention, this solution requires a 'dummy' row 4 to work. In A4 I put the formula =MAX(A1:A3)+1 and copied this across. The dummy row should have values higher than all of the other rows to work, so you can just use an arbitrarily large value.

[NBVC]

If your inputs are in H1:K1 and your data in A2:D4, perhaps:

=INDEX($E$2:$E$4,MATCH(1,INDEX((A2:A4>=H2)*(B2:B4>=I2)*(C2:C4>=J2)*(D2:D4>=K2),0),0))

adjust ranges to suit.

[jamphan]

That is close to what I need however the numbers can repeat for the different combinations so in my column A I may have 110 20 different times.

[NBVC]

Are you talking about both offered formulas?

Can you post a worksheet showing representative examples and some expected results?

[Andrew-R]

It shouldn't matter how many times a value repeats, the formula from both NBVC and myself should still work.

[jamphan]

Thanks NBVC. That worked perfectly!