Looking Up Two Values in an Array

[Mike M]

Here is my situation:
I am trying to compare the values I enter (width, height), compare them to a
table and retrieve a value in an associated third column.

So the question is, with these two dimensions (width 4.25, height 2) what
index value will they return? The correct answer is "B" because the width of
4.25 is greater than G5 so it most go to G6 and the height of 2 is smaller
than H6.

Now if the entered width is 8 and the entered height is 10.15 then the
answer would be "C". Because even though the width is equal to G6 the height
is greater than H6, so we have to go up to the next size.

So now to create a formula that can figure this out. Hopefully this makes
sense.

Here is a data sample:
B5(width), C5 (height)
4.25, 2

G5:G8 (widths)
4
8
12
17

H5:H8 (heights)
6
10
12
15.75

I5:I8 (Index)
A
B
C
D

Thanks for your help.

 

[T. Valko]

Try this array formula** :

=INDEX(I5:I8,MATCH(1,(G5:G8>=B5)*(H5:H8>=C5),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Shane Devenshire]

Please show us a sample of the table you are look at.

 

[Mike M]

Biff,

This appears to work perfectly. I can not quite figure out why, but
nonetheless it helps me to tackle a piece of the puzzle I have at hand.

Thank you!

Mike

 

[T. Valko]

You're welcome. Thanks for the feedback!