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

 

[Bosquedeguate]

What problem(s) were you having with VLOOKUP?
I added a VLOOKUP in Column B to your example without issues.

Change the formula if you want "Delete" to be displayed for values that are not found in the table.

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

Cell Formulas
Range	Formula
B2:B5	B2	=IF(VLOOKUP(A2,D2:G9,4,TRUE)=0,"NOT FOUND",VLOOKUP(A2,D2:G9,4,TRUE))

 

[afrazier1221]

Assuming the end value is the criteria, judging by the "Retain" in G8, which should only be a "Retain" at or above a $5,000 price point, you're looking for this:

In Cell G2:

=IF(F2<$A$3,$B$2,IF(AND(F2>$A$3-1,F2<$A$4),$B$3,IF(AND(F2>$A$4-1,F2<$A$5),$B$4,IF(F2>$A$5-1,$B$5,""))))

Once you have that in G2, copy the cell and paste it down the G column, from row 2 to 9.

The setup here uses cells rather than values, so you can change your price point criteria if you want, as well as your descriptors relative to the price points.

 

[ranjith2523]

What problem(s) were you having with VLOOKUP?
I added a VLOOKUP in Column B to your example without issues.

Change the formula if you want "Delete" to be displayed for values that are not found in the table.

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

Cell Formulas
Range	Formula
B2:B5	B2	=IF(VLOOKUP(A2,D2:G9,4,TRUE)=0,"NOT FOUND",VLOOKUP(A2,D2:G9,4,TRUE))

Thank you so much for your quick reply and sorry if i confused you with my statement.

Actually, i need to put a vlookup in G column and not in the B column (which I mentioned in the second line of my initial post).

E.g., The price 2400 (cell A2) falls between the range D4 to F4 and in G4 the formula should bring the value "Used" (Cell B2 value).

Please let me know if my explanation is not clear.

Regards,
Ranjith

 

[ranjith2523]

Assuming the end value is the criteria, judging by the "Retain" in G8, which should only be a "Retain" at or above a $5,000 price point, you're looking for this:

In Cell G2:

=IF(F2<$A$3,$B$2,IF(AND(F2>$A$3-1,F2<$A$4),$B$3,IF(AND(F2>$A$4-1,F2<$A$5),$B$4,IF(F2>$A$5-1,$B$5,""))))

Once you have that in G2, copy the cell and paste it down the G column, from row 2 to 9.

The setup here uses cells rather than values, so you can change your price point criteria if you want, as well as your descriptors relative to the price points.

Thanks for your reply.

If if copy paste it down the G column i am getting the results in all cells which is not correct .

It seems your formula refers only the cell F2 and it should refer to the range D to F.

E.g, The price 2400 (Cell A2) fall only in the range cell D4 to F4 so the "Used" should reflect only in G4 and not in G3 and G2.

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

 

[afrazier1221]

Thanks for your reply.

If if copy paste it down the G column i am getting the results in all cells which is not correct

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

The first two items were actually below the first price point. It just needs to be adjusted.
But item three is above 2400 and less than 3000. So it's Used.
Item four is above 2400, but below 3000. Used.
Item five is above 3000, but below 3750, so it's Match.
Item six is above 3000, but below 3750. Match.
Item seven is above 5000. Retain.
Item eight is above 5000. Retain.

You didn't really have any sort of specification for vehicles that fell below the 2400 price point.

 

[afrazier1221]

And again, as I specified, this was made with the assumption that the end value in Column F was the basis, per your example in G8. If the basis should be the Start or Mid value, all the F2 references would merely need to be changed to D2 or E2.

 

[ranjith2523]

The first two items were actually below the first price point. It just needs to be adjusted.
But item three is above 2400 and less than 3000. So it's Used.
Item four is above 2400, but below 3000. Used.
Item five is above 3000, but below 3750, so it's Match.
Item six is above 3000, but below 3750. Match.
Item seven is above 5000. Retain.
Item eight is above 5000. Retain.

You didn't really have any sort of specification for vehicles that fell below the 2400 price point.

Thank you so much for your explanation and effort.

But the requirement is different, the formula should return the value if the price falls within the range.

Thanks again for your time and help.

Regards,
Ranjith

 

[afrazier1221]

Thank you so much for your explanation and effort.

But the requirement is different, the formula should return the value if the price falls within the range.

Thanks again for your time and help.

Regards,
Ranjith

Whatever it is you're trying to do, I expect it's not difficult. I'm apparently misunderstanding what you're trying to make it do. Perhaps if you posted a table with typed in "results" as you would want it to appear so I can understand the end result you're trying to accomplish.

 

[ranjith2523]

Thanks again for your reply.

Even if i change the reference to D2 or E2, i will get the result in all G column cells but the requirement is if the A column price not fall in the particular range (D to F) the G column formula should reflect either as Blank or Not found. If the A column price falls between the range ((D to F)) the it should reflect the B column value (e.g, Used, Delete, Retain, etc.,)

And again, as I specified, this was made with the assumption that the end value in Column F was the basis, per your example in G8. If the basis should be the Start or Mid value, all the F2 references would merely need to be changed to D2 or E2