Xlookup Query

[mart_mrexcel]

In F4 I have a formula as =XLOOKUP(F2&F3,$A$2:$A$9&$B$2:$B$9,$C$2:$C$9,"",-1). My concern is, If I put range as 10, 11, 12, etc, it is returning the amount of 6500, the same thing happens if I put 20, 21, 22,etc it is returning as 5000, instead of 2000 (which in my expectation that anything >= 4 the value should be 2000.

	A	B	C	D	D	F
1	Group	Range	Amt
2	US	1	6500		Group	US
3	US	2	5000		Range	10
4	US	3	4000		Expected Amount	2000
5	US	4	2000
6	DS	1	6000
7	DS	2	4500
8	DS	3	3500
9	DS	4	1500

 

[Alex Blakenburg]

If you can rely on Range being in ascending order then this might work for you.

=XLOOKUP(1,(A2:A9=F2)*(B2:B9<=F3),C2:C9,"",0,-1)

 

[mart_mrexcel]

If you can rely on Range being in ascending order then this might work for you.

=XLOOKUP(1,(A2:A9=F2)*(B2:B9<=F3),C2:C9,"",0,-1)

Thank you alex, that works perfectly. Have a great day ahead

 

[Alex Blakenburg]

You're welcome. You too (it's the other end of the day down here in Australia )