Vlookup Using Less Than (<) Symbol in Table Data 1

[zinderellie]

Will Vlookup understand or recognize the use of the less than sign in the table data? For example <$1,000.

I'm getting N/A as my result. Example in file attached.

Thanks.

"If you can imagine it, you can achieve it.
If you can dream it, you can become it." -William Arthur Ward-

 

[SkipVought]

hi,

Is your first column in the lookup table SORTED ascending or descending?

I rarely use VLOOKUP, as it has some restrictions that MATCH & INDEX do not. Using the MATCH function, you can have your lookup column sorted either way, depending on what resulats you want, and then then INDEX can return a values from ANY column in your table...
[tt]
=INDEX(ReturnValueRange,MATCH(LookupValue,LookupRange,MatchType),1)
[/tt]

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zinderellie]

Hmmm... I tried this and got the same result... N/A. My data is sorted in Ascending order.

Name Sales Comm Rate Sales Range Comm %
A 10,000 15.00% <1000 0%
B 3,500 10.00% 1,000 10%
C 900 #N/A 5,000 15%

Columns are a,b,c, skip d, e, f.
My formula: =INDEX($E$2:$E$4,MATCH(B4,$E$2:$E$4,TRUE),1)



"If you can imagine it, you can achieve it.
If you can dream it, you can become it." -William Arthur Ward-

 

[SkipVought]

make your Sales Range data
[tt]
Sales Range
0
1000
5000
[/tt]
so the IMPLIED ranges are,
[tt]
0 - 999.99
1000 - 4999.99
5000 +
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}