Excel formula puzzle, looking up values based on range

[Excellation]

Hi all,

I've been breaking my head on creating a formula that calculates values in column Z. I have a reference table that contains data ranges as shown in column C in the reference table. I also have a database where I would like a formula to show me the category number from column B (1,2 or 3) from the reference table based on the values for Jane (column Y) in the database. For example, Jane has value 50 in the database. I then want the formula to go to the reference table and check based on the range (column C; 0-69.1 / 69.1-160.5 / >160.5) in what category value 50 falls (in this case the result would be 1 as the range data specifies the upper limit). Same for Iris where value 0.1 matches reference range 0.1 and then for the formula to show me category 1. One problem is that there aren't always 3 categories to choose from. Sometimes it's only 1 or 2 categories. Does anyone know how to do this using an excel formula?!

Thanks a lot!

Database:
[TABLE="width: 500"]
<tbody>[TR]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]50
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Iris
[/TD]
[TD]0.1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]100
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]


Reference table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]1
[/TD]
[TD]69.1
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]2
[/TD]
[TD]160.5
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]3
[/TD]
[TD]>160.5
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]1
[/TD]
[TD]9.6
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]2
[/TD]
[TD]17.9
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]3
[/TD]
[TD]>17.9
[/TD]
[/TR]
[TR]
[TD]Iris
[/TD]
[TD]1
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]Iris
[/TD]
[TD]2
[/TD]
[TD]0.5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

A slight change to column C in a reference table is required , see below :

Ctrl+Shift+Enter NOT just Enter

H2 =INDEX($B$2:$B$9,MATCH(MIN(IF(F2=$A$2:$A$9,ABS($C$2:$C$9-G2))),ABS(IF(F2=$A$2:$A$9,$C$2:$C$9-G2)),0))


[TABLE="width: 630"]
<colgroup><col width="70" span="9" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[TD="class: xl63, width: 70"]H[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"]Y[/TD]
[TD="class: xl63"]Z[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Jane[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]69.1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Jane[/TD]
[TD="class: xl63"]50[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Jane[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]160.5[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Iris[/TD]
[TD="class: xl63"]0.1[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]Jane[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]160.6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Tom[/TD]
[TD="class: xl63"]100[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Tom[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]9.6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]Tom[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]17.9[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]Tom[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]Iris[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]0.1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]Iris[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]0.5[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]