Simple question- Vlookup range within a cell

[jham55]

Hey folks, I have a quick question. In excel if you are doing a vlook up for a value and the look up column is a range in a cell, will excel recognize it? I'm looking for an option. So if my look up value is 2 and the column array is stored as '1-3' in a cell , I'm thinking it will not recognize it. any thoughts?

thanks
John

 

[jasonb75]

Enter the minimum value for each range in the cell rather than the actual range, sort the data by that column in ascending order then use approximate match.

Note that it may not work correctly if there are gaps in the ranges.

 

[jham55]

Enter the minimum value for each range in the cell rather than the actual range, sort the data by that column in ascending order then use approximate match.

Note that it may not work correctly if there are gaps in the ranges.

Thank you.. its possible but there are like 17K values to look up. Some of the values are in the middle, beginning or end of the range. I cant help that. I can break the range into single values but there are thousands of them.

 

[jasonb75]

It doesn't matter where they are in the range. Using the method that I suggested, any number greater than 1 but less then the next number in the column can be matched to 1, but no number can be matched to 1-3. I've included a simple example below.

Book1
	A	B	C	D	E
1	Minimum	Range		2	1-3
2	1	1-3		4	4-19
3	4	4-19		25	>20
4	20	>20		99999999	>20
Sheet1

Cell Formulas
Range	Formula
E1:E4	E1	=VLOOKUP(D1,$A$2:$B$4,2)

 

[jham55]

Ahh I see what you are saying. I will give this a shot. Thanks!