# 2 lookups

#### Johnboy28

##### Board Regular
Hi, Does anyone know a formula that match Client ID & weight and return correct Weight Range?

 Client ID Weight Weight Range Home 21.05 ?? Local 28.50 ?? Weight Range Lower Weight Range Upper Client ID Weight Range 1.0000 26.9900 Local Up to 27kg 22.9901 42.9900 Local 27kg up 17.9901 22.9900 Home 18kg - 23kg 22.9901 26.9900 Home 23kg-27kg 26.9901 30.9900 Home 27kg-31kg 30.9901 42.9900 Home 31kg up 42.9901 150.0000 Home 42kg up

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### FormR

##### MrExcel MVP
Hi, here is an option to try:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Client ID</td><td style=";">Weight</td><td style=";">Weight Range</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Home</td><td style="text-align: right;;">21.05</td><td style="background-color: #FFFF00;;">18kg - 23kg</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Local</td><td style="text-align: right;;">28.5</td><td style=";">27kg up</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Weight Range Lower</td><td style=";">Weight Range Upper</td><td style=";">Client ID</td><td style=";">Weight Range</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">26.99</td><td style=";">Local</td><td style=";">Up to 27kg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">22.9901</td><td style="text-align: right;;">42.99</td><td style=";">Local</td><td style=";">27kg up</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">17.9901</td><td style="text-align: right;;">22.99</td><td style=";">Home</td><td style=";">18kg - 23kg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">22.9901</td><td style="text-align: right;;">26.99</td><td style=";">Home</td><td style=";">23kg-27kg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">26.9901</td><td style="text-align: right;;">30.99</td><td style=";">Home</td><td style=";">27kg-31kg</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">30.9901</td><td style="text-align: right;;">42.99</td><td style=";">Home</td><td style=";">31kg up</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">42.9901</td><td style="text-align: right;;">150</td><td style=";">Home</td><td style=";">42kg up</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=LOOKUP(<font color="Blue">B2,\$A\$6:\$A\$12/(<font color="Red">\$C\$6:\$C\$12=A2</font>),\$D\$6:\$D\$12</font>)</td></tr></tbody></table></td></tr></table><br />

#### Johnboy28

##### Board Regular
Hi, here is an option to try:

Excel 2013/2016
ABCD
1Client IDWeightWeight Range
2Home21.0518kg - 23kg
3Local28.527kg up
4
5Weight Range LowerWeight Range UpperClient IDWeight Range
6126.99LocalUp to 27kg
722.990142.99Local27kg up
817.990122.99Home18kg - 23kg
922.990126.99Home23kg-27kg
1026.990130.99Home27kg-31kg
1130.990142.99Home31kg up
1242.9901150Home42kg up

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=LOOKUP(B2,\$A\$6:\$A\$12/(\$C\$6:\$C\$12=A2),\$D\$6:\$D\$12)

</tbody>

<tbody>
</tbody>
Thanks for the reply. That formula works great. Thanks again.

1,102,694
Messages
5,488,314
Members
407,634
Latest member
ps01

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...