# Return value from lookup table

#### Deutz

##### Board Regular

I am attempting to create a formula that will take a value entered by the user and return a conditionally matching value from a lookup table. There are two columns in the lookup table as per the example below ...

 match​ x​ 55​ 0.1600​ 400​ 0.3800​ 658​ 0.5780​ 2354​ 0.6370​ 7600​ 0.4500​ 7600 & over​ 0.4900​

<tbody>
</tbody>

If the value entered is …

< 55 then return 0.1600

>= 55 and < 400 then return 0.3800

>= 400 and < 658 then return 0.5780

>= 658 and < 2354 then return 0.6370

>= 2354 and < 7600 then return 0.4500

>= 7600 then return 0.4900

I know it would make sense to redo the table with To and From columns to make it a bit easier so the range is defined clearly, but unfortunately I have dozens of tables set out the same as this one, which I need to produce a formula for, and am unable to change them, so I can't do it that way. I have to work with that horrible format.

Thanks
Deutz

Last edited:

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Marcelo Branco

##### MrExcel MVP
If you cannot change the tables defining more convenient tracks, maybe this workaround

 A​ B​ C​ D​ E​ 1​ match​ x​ User Entries​ Results​ 2​ 55​ 0.1600​ 10​ 0.1600​ 3​ 400​ 0.3800​ 55​ 0.3800​ 4​ 658​ 0.5780​ 399​ 0.3800​ 5​ 2354​ 0.6370​ 400​ 0.5780​ 6​ 7600​ 0.4500​ 659​ 0.6370​ 7​ 7600 & over​ 0.4900​ 2354​ 0.4500​ 8​ 7599​ 0.4500​ 9​ 7600​ 0.4900​

Formula in E2 copied down
=INDEX(B\$2:B\$7,IFERROR(MATCH(D2,A\$2:A\$6)+1,1))

Hope this helps

M.

##### MrExcel MVP
Is it admmissable to you that we add a 0 record in front, i.e. 0 >> 0.1600?

#### Marcelo Branco

##### MrExcel MVP
Another formula

E2 copied down
=IF(A\$2>D2,B\$2,LOOKUP(D2,A\$2:A\$6,B\$3:B\$7))
<a\$2,b\$2,lookup(d2,a\$2:a\$6,b\$3:b\$7))

M.</a\$2,b\$2,lookup(d2,a\$2:a\$6,b\$3:b\$7))

Last edited:

#### Deutz

##### Board Regular
Thanks Marcelo, those workarounds do the trick nicely!

Thanks Aladin, but I can't change anything in the lookup tables.

Cheers
Deutz

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

Replies
9
Views
761
Replies
1
Views
346
Replies
1
Views
401
Replies
6
Views
388
Replies
3
Views
650

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back