| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing approx values | Excel Discussion (Misc queries) | |||
| put approx. 4 price list on i 1 piece paper with clip art | Setting up and Configuration of Excel | |||
| Return alternate value if VLookup can't find match | Excel Worksheet Functions | |||
| match a name and return a tel number? | Excel Discussion (Misc queries) | |||
| annual leave planner for approx 100 staff members | Excel Discussion (Misc queries) | |||
Linear Mode
