2 lookups

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
120
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

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
Joined
Aug 18, 2011
Messages
6,413
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
120
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,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...
Top