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.

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.


--
Biff
Microsoft Excel MVP


"Mike M" wrote in message
...
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.

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike M" wrote:

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.

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

Unfortunately, LOOKUP rounds DOWN, so you'll also need to renumber the
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.001
and the values 6, 10, 12, 15.75 will change to 0, 6.001, 10.001, 12.001

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
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46465

T. Valko;167768 Wrote:
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.

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46465

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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Mike M" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mike M" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Mike M" wrote in message
...
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.