If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
vlookup is returning a value one cell above the correct cell.
i have a simple vlookup formula in one worksheet referenceing values in
another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) ' the data has been sorted, but the vlookup function continues to return a value from 'otherworksheet' that is physically one cell above the corect cell. for example, if the value in B2 is found in row 23 of 'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i have never had a problem like this. i have resorted the data several times (ascending) and tried the regular lookup function, but i keep getting the value ove cell above the target cell. thoughts? |
#2
|
|||
|
|||
vlookup is returning a value one cell above the correct cell.
If the value is not exact it will return the next largest value that is less
than lookup using your setup, if you are only looking for exact matches use FALSE or 0 =vlookup(B2,otherworksheet!A237,3,FALSE) or =vlookup(B2,otherworksheet!A237,3,0) then it will return an error if not exact match is found and the lookup range need not to be sorted -- Regards, Peo Sjoblom http://nwexcelsolutions.com "dbaker4" wrote in message ... i have a simple vlookup formula in one worksheet referenceing values in another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) ' the data has been sorted, but the vlookup function continues to return a value from 'otherworksheet' that is physically one cell above the corect cell. for example, if the value in B2 is found in row 23 of 'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i have never had a problem like this. i have resorted the data several times (ascending) and tried the regular lookup function, but i keep getting the value ove cell above the target cell. thoughts? |
#3
|
|||
|
|||
vlookup is returning a value one cell above the correct cell.
the lookup value, or B2, is actually a three letter equity ticker (MMM for 3M
Company). there is only one equity ticker per public company in the table array. the ' =vlookup() ' formula is successfully finding the corresponding ticker in the 'otherworksheet' but for every ticker the equation is returning a value from one row above the correct cell. it is happening for every ticker (~125 tickers) and the problem is the same. therefore, there is some sort of systematic error. when i try to use 'FALSE' or '0' as the range lookup, i get the #N/A response. however, the tickers in the lookup value and in the reference sheet are the exact same, so the equation should find a value when i enter 'FALSE' or '0'... "Peo Sjoblom" wrote: If the value is not exact it will return the next largest value that is less than lookup using your setup, if you are only looking for exact matches use FALSE or 0 =vlookup(B2,otherworksheet!A237,3,FALSE) or =vlookup(B2,otherworksheet!A237,3,0) then it will return an error if not exact match is found and the lookup range need not to be sorted -- Regards, Peo Sjoblom http://nwexcelsolutions.com "dbaker4" wrote in message ... i have a simple vlookup formula in one worksheet referenceing values in another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) ' the data has been sorted, but the vlookup function continues to return a value from 'otherworksheet' that is physically one cell above the corect cell. for example, if the value in B2 is found in row 23 of 'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i have never had a problem like this. i have resorted the data several times (ascending) and tried the regular lookup function, but i keep getting the value ove cell above the target cell. thoughts? |
#4
|
|||
|
|||
vlookup is returning a value one cell above the correct cell.
If you get an error using FALSE then they are not exact, you can easily test
that be comparing them =EXACT(A1,C1) or even =A1=C1 will return TRUE if they are. All it takes is an extra space or some html crap if imported from a web page or intranet. If the formula returns true then you must have messed up the table dimensions you should always use FALSE if you are looking for exact matches, if not you will get erroneous data, I prefer never to use TRUE (which is what you do when leaving it empty) with text, it is very handy when it comes to numbers when you are looking for exact or closest smaller value. -- Regards, Peo Sjoblom http://nwexcelsolutions.com "dbaker4" wrote in message news the lookup value, or B2, is actually a three letter equity ticker (MMM for 3M Company). there is only one equity ticker per public company in the table array. the ' =vlookup() ' formula is successfully finding the corresponding ticker in the 'otherworksheet' but for every ticker the equation is returning a value from one row above the correct cell. it is happening for every ticker (~125 tickers) and the problem is the same. therefore, there is some sort of systematic error. when i try to use 'FALSE' or '0' as the range lookup, i get the #N/A response. however, the tickers in the lookup value and in the reference sheet are the exact same, so the equation should find a value when i enter 'FALSE' or '0'... "Peo Sjoblom" wrote: If the value is not exact it will return the next largest value that is less than lookup using your setup, if you are only looking for exact matches use FALSE or 0 =vlookup(B2,otherworksheet!A237,3,FALSE) or =vlookup(B2,otherworksheet!A237,3,0) then it will return an error if not exact match is found and the lookup range need not to be sorted -- Regards, Peo Sjoblom http://nwexcelsolutions.com "dbaker4" wrote in message ... i have a simple vlookup formula in one worksheet referenceing values in another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) ' the data has been sorted, but the vlookup function continues to return a value from 'otherworksheet' that is physically one cell above the corect cell. for example, if the value in B2 is found in row 23 of 'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i have never had a problem like this. i have resorted the data several times (ascending) and tried the regular lookup function, but i keep getting the value ove cell above the target cell. thoughts? |
#5
|
|||
|
|||
vlookup is returning a value one cell above the correct cell.
I have the same problem. I'm matching imprecise text and it's finding the
right match but returning a cell above. I've decided to fix it by hand since the program appears to be inflexible in this situation. I just added another column and made it equal the cell below. "dbaker4" wrote: i have a simple vlookup formula in one worksheet referenceing values in another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) ' the data has been sorted, but the vlookup function continues to return a value from 'otherworksheet' that is physically one cell above the corect cell. for example, if the value in B2 is found in row 23 of 'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i have never had a problem like this. i have resorted the data several times (ascending) and tried the regular lookup function, but i keep getting the value ove cell above the target cell. thoughts? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Urgent date/scheduling calc needed | jct | Worksheet Functions | 3 | February 24th, 2006 01:36 AM |
Using a cell reference of a sheet in Vlookup | crazybass2 | Worksheet Functions | 3 | August 12th, 2005 07:51 PM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
VLOOKUP returning incorrect value | Sandra | Worksheet Functions | 4 | December 3rd, 2003 07:43 PM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |