Need to VLOOKUP within Range

[ranjith2523]

Hi Friends,

I need to apply a vlookup or other alternate formula to lookup a price within a range and get the output based on the match.

In the below table, I have the price in column A and the value in column B. We have price range data in the D to F column and in the G column I need to put a lookup formula.

For example, if you look into cell G4 it appears as "Used" (cell B2) since the price 2400 (cell A2) falls between range 2359 to 2465. If there is no match then we can leave the cells as blank.

I tried to apply vlookup formula using "Approximate Match" but it didn't work out for me .

Can someone please help me to sort it out.

Price	Value		Start	Mid	End	Value
2400	Used		2,158	2,207	2,255
3000	Match		2,256	2,307	2,358
3750	Delete		2,359	2,412	2,465	Used
5000	Retain		2,466	2,522	2,577
			2,945	3,012	3,078	Match
			3,079	3,149	3,218
			4,793	4,901	5,009	Retain
			5,010	5,123	5,235

Regards,
Ranjith

 

[afrazier1221]

So D, E, and F are the range? A is the price you're manually inputting? And B is the column that needs to change based upon the values in column A? Is that correct?

 

[ranjith2523]

So D, E, and F are the range? A is the price you're manually inputting? And B is the column that needs to change based upon the values in column A? Is that correct?

Really sorry, i think i need to explain better.

Yes D, E and F are the range.
Yes, A is the price which we manually input.
NO, B is also a manual input value.

Actually, everything is a manual input except column G. Let me try to explain better.

In below table, i have changed the headers for better understanding. We need to put a formula in Column G to see what prize for what Lottery Number Range.

Lottery Number	Prize		Start	Mid	End	Prize
2400	Headset		2,158	2,207	2,255	NO_PRIZE
3000	TV		2,256	2,307	2,358	NO_PRIZE
5000	Radio		2,359	2,412	2,465	Headset
			2,466	2,522	2,577	NO_PRIZE
			2,945	3,012	3,078	TV
			3,079	3,149	3,218	NO_PRIZE
			4,793	4,901	5,009	NO_PRIZE
			5,010	5,123	5,235	Radio

 

[ranjith2523]

Can someone please help me with this? I tried to write the formula but I failed

I have been doing this manually for the past couple of hours.

Regards,
Ranjith

 

[Alex Blakenburg]

See if one of these works for you:

Book1
	A	B	C	D	E	F	G	H
1	Price	Value		Start	Mid	End	Value Xlookup	Value Filter
2	2400	Used		2158	2207	2255
3	3000	Match		2256	2307	2358
4	3750	Delete		2359	2412	2465	Used	Used
5	5000	Retain		2466	2522	2577
6				2945	3012	3078	Match	Match
7				3079	3149	3218
8				4793	4901	5009	Retain	Retain
9				5010	5123	5235
Data

Cell Formulas
Range	Formula
G2:G9	G2	=XLOOKUP(1,($A$2:$A$5>=D2)*($A$2:$A$5<=F2),$B$2:$B$5,"")
H2:H9	H2	=FILTER($B$2:$B$5,($A$2:$A$5>=D2)*($A$2:$A$5<=F2),"")

 

[ranjith2523]

See if one of these works for you:

Book1
	A	B	C	D	E	F	G	H
1	Price	Value		Start	Mid	End	Value Xlookup	Value Filter
2	2400	Used		2158	2207	2255
3	3000	Match		2256	2307	2358
4	3750	Delete		2359	2412	2465	Used	Used
5	5000	Retain		2466	2522	2577
6				2945	3012	3078	Match	Match
7				3079	3149	3218
8				4793	4901	5009	Retain	Retain
9				5010	5123	5235
Data

Cell Formulas
Range	Formula
G2:G9	G2	=XLOOKUP(1,($A$2:$A$5>=D2)*($A$2:$A$5<=F2),$B$2:$B$5,"")
H2:H9	H2	=FILTER($B$2:$B$5,($A$2:$A$5>=D2)*($A$2:$A$5<=F2),"")

Oh My God !!! You are just awesome

The below formula works as expected and it meets my requirements Thank you so much, Mr. Alex.

Just to gain my knowledge, could you please break the formulas into separate pieces and explain to me how it work? Your explanation will also help other people to gain some knowledge.

=XLOOKUP(1,($A$2:$A$5>=D2)*($A$2:$A$5<=F2),$B$2:$B$5,"")

 

[Alex Blakenburg]

The components I used in the Xlookup are:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)
The key part is the lookup_array. Here we put multiple conditions linked by multiplication "*".
Each condition returns an array of Trues and Falses.
The multiplication then does 2 things it converts True to 1 and False to 0 and then by the act of mutliplying performs an AND condition since only 1 * 1 (all are true) will return 1 anything else will return 0.
(your condition being that you want the value in column A to be between the value in D & F)

Since we now have an array of 1s and 0s the lookup_value needs to be 1.

You will find a very good complete more explanation here:

XLOOKUP with multiple criteria

The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1...

exceljet.net

 

[ranjith2523]

The components I used in the Xlookup are:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)
The key part is the lookup_array. Here we put multiple conditions linked by multiplication "*".
Each condition returns an array of Trues and Falses.
The multiplication then does 2 things it converts True to 1 and False to 0 and then by the act of mutliplying performs an AND condition since only 1 * 1 (all are true) will return 1 anything else will return 0.
(your condition being that you want the value in column A to be between the value in D & F)

Since we now have an array of 1s and 0s the lookup_value needs to be 1.

You will find a very good complete more explanation here:

XLOOKUP with multiple criteria

The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1...

exceljet.net

Perfect Mr.Alex.. Thank you so much for your help

 

[Alex Blakenburg]

You’re welcome. Glad we could help.

 

[smozgur]

Perfect Mr.Alex.. Thank you so much for your help

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.