2 lookups

[Johnboy28]

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

[TABLE="width: 497"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Weight[/TD]
[TD]Weight Range[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]21.05[/TD]
[TD] ??[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD]28.50[/TD]
[TD] ??[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weight Range Lower[/TD]
[TD]Weight Range Upper[/TD]
[TD]Client ID[/TD]
[TD]Weight Range[/TD]
[/TR]
[TR]
[TD]1.0000[/TD]
[TD]26.9900[/TD]
[TD]Local[/TD]
[TD]Up to 27kg[/TD]
[/TR]
[TR]
[TD]22.9901[/TD]
[TD]42.9900[/TD]
[TD]Local[/TD]
[TD]27kg up[/TD]
[/TR]
[TR]
[TD]17.9901[/TD]
[TD]22.9900[/TD]
[TD]Home[/TD]
[TD]18kg - 23kg[/TD]
[/TR]
[TR]
[TD]22.9901[/TD]
[TD]26.9900[/TD]
[TD]Home[/TD]
[TD]23kg-27kg[/TD]
[/TR]
[TR]
[TD]26.9901[/TD]
[TD]30.9900[/TD]
[TD]Home[/TD]
[TD]27kg-31kg[/TD]
[/TR]
[TR]
[TD]30.9901[/TD]
[TD]42.9900[/TD]
[TD]Home[/TD]
[TD]31kg up[/TD]
[/TR]
[TR]
[TD]42.9901[/TD]
[TD]150.0000[/TD]
[TD]Home[/TD]
[TD]42kg up[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, here is an option to try:

Excel 2013/2016
	A	B	C	D
1	Client ID	Weight	Weight Range
2	Home	21.05	18kg - 23kg
3	Local	28.5	27kg up
4
5	Weight Range Lower	Weight Range Upper	Client ID	Weight Range
6	1	26.99	Local	Up to 27kg
7	22.9901	42.99	Local	27kg up
8	17.9901	22.99	Home	18kg - 23kg
9	22.9901	26.99	Home	23kg-27kg
10	26.9901	30.99	Home	27kg-31kg
11	30.9901	42.99	Home	31kg up
12	42.9901	150	Home	42kg up
Sheet1

Cell Formulas
Range	Formula
C2	=LOOKUP(B2,$A$6:$A$12/($C$6:$C$12=A2),$D$6:$D$12)

 

[Johnboy28]

Hi, here is an option to try:

Excel 2013/2016

	A	B	C	D
Client ID	Weight	Weight Range
Home
Local	27kg up
Weight Range Lower	Weight Range Upper	Client ID	Weight Range
Local	Up to 27kg
Local	27kg up
Home	18kg - 23kg
Home	23kg-27kg
Home	27kg-31kg
Home	31kg up
Home	42kg up

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]21.05[/TD]
[TD="bgcolor: #FFFF00"]18kg - 23kg[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]28.5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26.99[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]22.9901[/TD]
[TD="align: right"]42.99[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]17.9901[/TD]
[TD="align: right"]22.99[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]22.9901[/TD]
[TD="align: right"]26.99[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]26.9901[/TD]
[TD="align: right"]30.99[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]30.9901[/TD]
[TD="align: right"]42.99[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]42.9901[/TD]
[TD="align: right"]150[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=LOOKUP(B2,$A$6:$A$12/($C$6:$C$12=A2),$D$6:$D$12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the reply. That formula works great. Thanks again.