2 lookups

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
118
Office Version
365, 2016, 2013, 2010
Platform
Windows
Hi, Does anyone know a formula that match Client ID & weight and return correct Weight Range?

Client IDWeightWeight Range
Home21.05 ??
Local28.50 ??
Weight Range LowerWeight Range UpperClient IDWeight Range
1.000026.9900LocalUp to 27kg
22.990142.9900Local27kg up
17.990122.9900Home18kg - 23kg
22.990126.9900Home23kg-27kg
26.990130.9900Home27kg-31kg
30.990142.9900Home31kg up
42.9901150.0000Home42kg up

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,408
Office Version
365
Platform
Windows
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
Joined
Jun 22, 2013
Messages
118
Office Version
365, 2016, 2013, 2010
Platform
Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

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

Watch MrExcel Video

Forum statistics

Threads
1,099,803
Messages
5,470,876
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top