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.

 

[JBeaucaire]

I would make a lot of changes to the layout to get the data into
standard table. This means you will need to figure out the answers onc
for each pair and populate the table. After that, a simple INDEX/LOOKU
will do the trick

Unfortunately, LOOKUP rounds DOWN, so you'll also need to renumber th
range to show the LOWER number of each range.

So the values 4, 8, 12, 17 will change to 0, 4.001, 8.001, 12.00
and the values 6, 10, 12, 15.75 will change to 0, 6.001, 10.001, 12.00

This workbook shows how it looks up the values. This is one approach

+-------------------------------------------------------------------
|Filename: NewTable.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=58
+-------------------------------------------------------------------

 

[JBeaucaire]

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 o
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and th
SHIF
key then hit ENTER

This appears to do the trick. Nice, very nice. Need verification tha
all combinations provide the desired INDEX since we only received th
one example

 

[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!