Vlookup does not return approx match
 

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.

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.

Vlookup does not return approx match
 

It Vlookup returns 0, as opposed to #N/A, that means it found a match.

Your Vlookup asks for Column G of the row which has Location11. What's in column
G of that row? 0 or something else?

--
Regards,
Fred


"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.

Vlookup does not return approx match
 

The data is coming from an external database so I don't have control over the
format coming from the source. I have two worksheets containing data from
different tables. The data is refreshed each time the workbook is opened.
The number concatenated to the "location" is a billing level. The billing
level is retrieved using a vlookup. The billing level retrieved is then
concatenated with the location selected by the user to retrieve the billing
rate.

As a test, I changed just the data to add the zero as you suggested and
resorted the data. So now the order is
Location03
Location06
Location09
Location11
Location13
Location14
vlookup of Location12 still returns zero.

"Teethless mama" wrote:

You have to change to two digit number
example: Location3 change to Location03 and so on...
then sort ascending order, then use your Vlookup formula.


"djd" wrote:

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.

Vlookup does not return approx match
 

You have to change to two digit number
example: Location3 change to Location03 and so on...
then sort ascending order, then use your Vlookup formula.


"djd" wrote:

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.

Vlookup does not return approx match
 

Something else. Strange thing is that if I change the range lookup to False
and the Location & billing level matches what is in the table I get the
biling rate. If I change the range lookup to True and use the exact same
values I get a zero.


"Fred Smith" wrote:

It Vlookup returns 0, as opposed to #N/A, that means it found a match.

Your Vlookup asks for Column G of the row which has Location11. What's in column
G of that row? 0 or something else?

--
Regards,
Fred


"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.

Vlookup does not return approx match
 

You have to change to two digit number
example: Location3 change to Location03 and so on...
then sort ascending order, then use your Vlookup formula.

No you don't!

Biff

"Teethless mama" wrote in message
...
You have to change to two digit number
example: Location3 change to Location03 and so on...
then sort ascending order, then use your Vlookup formula.


"djd" wrote:

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.

Vlookup does not return approx match
 

You're probably getting 0 because your formula uses entire columns in the
lookup_table and you're picking up an empty cell at the very bottom of the
table. That's usually what happens (but not always) when you use TRUE as the
range_lookup argument and there is no "closest match".

Biff

"djd" wrote in message
...
Something else. Strange thing is that if I change the range lookup to
False
and the Location & billing level matches what is in the table I get the
biling rate. If I change the range lookup to True and use the exact same
values I get a zero.


"Fred Smith" wrote:

It Vlookup returns 0, as opposed to #N/A, that means it found a match.

Your Vlookup asks for Column G of the row which has Location11. What's in
column
G of that row? 0 or something else?

--
Regards,
Fred


"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.