Help Me! - Complex Lookup Table

charlesdlm · Oct 2, 2008

></o

>[/FONT]

[FONT=&quot]From Size[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; width: 47pt; height: 16.5pt;" width="63">

[FONT=&quot]To
Size[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; width: 44pt; height: 16.5pt;" width="59">

[FONT=&quot]Zone1[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; width: 44pt; height: 16.5pt;" width="59">

[FONT=&quot]Zone2[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]86[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.2485[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1845[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]86[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]180[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1855[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1353[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]180[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]360[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1586[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1074[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]360[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]540[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1333[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0889[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]540[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]720[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1210[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0812[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]720[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]900[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0937[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0639[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]900[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1080[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.1094[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0742[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1080[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1440[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0942[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.0649[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;"></td> <td style="padding: 0cm; height: 15pt;"></td> <td style="padding: 0cm; height: 15pt;"></td> <td style="padding: 0cm; height: 15pt;"></td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0cm; height: 15.75pt;"></td> <td colspan="2" style="padding: 0cm; height: 15.75pt;"> [FONT=&quot]What to lookup[/FONT][FONT=&quot]<o

></o

>[/FONT]
</td> <td style="padding: 0cm; height: 15.75pt;"></td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;"></td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Size[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Zone[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Answer[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;"></td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]50[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]0.2485[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0cm; height: 15.75pt;"></td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]1000[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]2[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]0.0742[/FONT][FONT=&quot]<o

></o

>[/FONT]

</td> </tr> </tbody></table>

Brian from Maui · Oct 2, 2008

Try using VLOOKUP defaulting to True with the From Size column as the lookup column.

Andrew Poulsom · Oct 2, 2008

If your lookup table is in the range A1:D9 with headers in row 1:

=VLOOKUP(50,A2:D9,MATCH("Zone1",A1:D1,FALSE))
=VLOOKUP(1000,A2:D9,MATCH("Zone2",A1:D1,FALSE))

The values to be looked up and matched can be cell references. You don't need the To Size column, because VLOOKUP will use the nearest value below if lookup value is not in the table.

iliace · Oct 2, 2008

Excel Workbook

A

B

C

D

1

From Size

To*

Zone1

Zone2

2

Size

3

0

86

0.2485

0.1845

4

86

180

0.1855

0.1353

5

180

360

0.1586

0.1074

6

360

540

0.1333

0.0889

7

540

720

0.121

0.0812

8

720

900

0.0937

0.0639

9

900

1080

0.1094

0.0742

10

1080

1440

0.0942

0.0649

11

12

13

14

Size

Zone

15

50

1

0.2485

16

400

2

0.0889

sheet

ajocius1 · Oct 2, 2008

I would use simple MATCH formual and then IF condition and INDEX formula.

Assuming your numbers are in area A3:D10, and condition area is B13:C14, then enter following formulas. In cell D13 enter : =MATCH(B13;$A$3:$A$10;1) , then in cell E13 enter formula : =MATCH(B13;$A$3:$A$10;1) . Now you get your answer and you can have as many conditions and copy the formula down.

Help Me! - Complex Lookup Table

charlesdlm

New Member

Excel Facts

Brian from Maui

MrExcel MVP

Andrew Poulsom

MrExcel MVP

iliace

Well-known Member

ajocius1

Board Regular

Similar threads

Forum statistics

Share this page

Help Me! - Complex Lookup Table

charlesdlm

New Member

Excel Facts

Brian from Maui

MrExcel MVP

Andrew Poulsom

MrExcel MVP

iliace

Well-known Member

ajocius1

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock