Lookup across multiple sheets

[NSG185]

I've got a workbook with width (x value) and height (y dimensions) that has corresponding values for each unique size combination. The x values are the same on every sheet, but the y values change ranges for each sheet (i.e. Sheet 1:0-10", Sheet2:11-20", etc.)

I have a lookup (VLOOKUP + MATCH) function to return the value for the sizes entered.

How do create a lookup that will check thru each sheet?

SHEET1
<table border="0" cellpadding="0" cellspacing="0" width="378"><col style="width: 59pt;" width="78"> <col style="width: 56pt;" width="75"> <col style="width: 56pt;" span="3" width="75"> <tbody><tr style="height: 20.1pt;" height="26"> <td class="xl24" style="height: 20.1pt; width: 59pt;" height="26" width="78">
</td> <td class="xl25" style="width: 56pt;" width="75">0.06250</td> <td class="xl25" style="width: 56pt;" width="75">0.12500</td> <td class="xl25" style="width: 56pt;" width="75">0.18750</td> <td class="xl25" style="width: 56pt;" width="75">0.25000</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl23" style="height: 13.5pt;" height="18">0.06250</td> <td class="xl22">1</td> <td class="xl22">3,216</td> <td class="xl22">6,431</td> <td class="xl22">9,646</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">0.12500</td> <td class="xl22">2</td> <td class="xl22">3,217</td> <td class="xl22">6,432</td> <td class="xl22">9,647</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">0.18750</td> <td class="xl22">3</td> <td class="xl22">3,218</td> <td class="xl22">6,433</td> <td class="xl22">9,648</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">0.25000</td> <td class="xl22">4</td> <td class="xl22">3,219</td> <td class="xl22">6,434</td> <td class="xl22">9,649</td> </tr> </tbody></table>

SHEET2
<table border="0" cellpadding="0" cellspacing="0" width="378"><col style="width: 59pt;" width="78"> <col style="width: 56pt;" width="75"> <col style="width: 56pt;" span="3" width="75"> <tbody><tr style="height: 20.1pt;" height="26"> <td class="xl26" style="height: 20.1pt; width: 59pt;" height="26" width="78">
</td> <td class="xl28" style="width: 56pt;" width="75">11.00000</td> <td class="xl27" style="width: 56pt;" width="75">11.06250</td> <td class="xl27" style="width: 56pt;" width="75">11.12500</td> <td class="xl27" style="width: 56pt;" width="75">11.18750</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" height="18">0.06250</td> <td class="xl24">562,626</td> <td class="xl24">565,841</td> <td class="xl24">569,056</td> <td class="xl24">572,271</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">0.12500</td> <td class="xl24">562,627</td> <td class="xl24">565,842</td> <td class="xl24">569,057</td> <td class="xl24">572,272</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">0.18750</td> <td class="xl24">562,628</td> <td class="xl24">565,843</td> <td class="xl24">569,058</td> <td class="xl24">572,273</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">0.25000</td> <td class="xl24">562,629</td> <td class="xl24">565,844</td> <td class="xl24">569,059</td> <td class="xl24">572,274</td> </tr> </tbody></table>
Thanks for your help!

 

[ukmikeb]

Hi

You could determine the sheet to use with the following formula -

=IF(INT(A1/10)=A1/10,INT(A1/10),INT(A1/10)+1)

where A1 contains the requisite dimension.

hth

Mike

 

[Aladin Akyurek]

Name the table on Sheet1 TableA, the table on Sheet2 TableB, etc. Note that a table name must include x-values, y-values, and the associated values.

Given on a different sheet:

A2: 0.17 (width)

B2: 11.08 (height)

invoke in C2...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
    VLOOKUP(A2,TableA,MATCH(B2,INDEX(TableA,1,0),1),1),
    VLOOKUP(A2,TableB,MATCH(B2,INDEX(TableA,1,0),1),1)))