2 lookups

[Johnboy28]

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>

 

[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
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

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

Sheet1​

Worksheet Formulas Cell Formula 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.