View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Vlookup does not return approx match

It works ok for me. I'm assuming RatesByLevel column A has these entries in
order:

Location11
Location13
Location14
Location3
Location6
Location9


But, why concatenate the lookup_value?

Biff

"djd" wrote in message
...
I am having a problem where vlookup is not returning the next largest value
that is less than the lookup value for some reason. The key to the data
is
a column that contains a concatenated key since the values I am trying to
match on are not in contiguous columns. The data is sorted in ascending
order.
Here is are the key values I am trying to searching on:

Location11
Location13
Location14
Location3
Location6
Location9

When I try to find Location12 using vlookup, I am expecting that it will
find Location11 but instead a 0 is returned.
I have used the IsNumber function to verify that the numbers are truly
numbers in the data table as well as in the values I am passing in the
vlookup statement.

Here is the vlookup statement: =VLOOKUP(J6&K6,RatesByLevel!A:H,7,TRUE)

I have used Trim on the contents of J6 to make sure there are no blanks as
well as the text values in the key data above.