VLOOKUP
-
I had munged some data from a customer, and they came back and said that some of what I'd done was wrong. So I put the original excel sheet I'd receieved, and the one I sent back into the same workbook. I used the VLOOKUP function to grab some data from one sheet to the other to prove that my results were correct. But lots of the items were coming up with "#N/A", which means that excel couldn't find the entry.
Spent a lot of time trying to figure out WTF was going on, including copying and repasting and lookup up using different ranges. Using other formulae, like "=A2=B2", which evaluated to TRUE, meaning that excel knew the values were correct, but it still wouldn't find the value. It eventually occurred to me to take a closer look at the values, and I noticed that the values all included multiple spaces inside the text (I'd already trimmed the trailing spaces).
The syntax for the function looks like this:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Apparently the VLOOKUP function in excel can't handle a value in the table array where there are multiple spaces. It was OK to leave the spaces in the lookup_value. So I had to make the values different for VLOOKUP to think they were the same. Knowing the product, I can't figure out if this is a feature or a bug.
-
Why not just throw a little VBA in there and run a LTrim and RTrim on the cells?
-
You need to create a new business rule stating that no product description (or any field) can have more than one space.
@boomzilla said:
Knowing the product, I can't figure out if this is a feature or a bug.
The world may never know.
-
Are you saying that Excel couldn't match vlookups with values like 'A B C', 'X Y Z' and that you needed to make them 'A_B_C', 'X_Y_Z' or something?
-
I have extensive knowledge of Excel and use VLookup, Hlookup, vba, etc extensively and have yet to run across this issue you are describing. I tried simulating it on my end and found no problems with VLookup identifying items with multiple spaces within the table array. Without further clarification of your exact situation, I would assume it is user error, or missunderstanding of how the function works.
That being said, Excel does have a few bugs that are apparent, notably in the RANK() function, so there is the possibility you have found a new, undocumented bug.
-
@Jonathan Holland said:
Why not just throw a little VBA in there and run a LTrim and RTrim on the cells?
@lpope187 said:
Are you saying that Excel couldn't match vlookups with values like 'A B C', 'X Y Z' and that you needed to make them 'A_B_C', 'X_Y_Z' or something?
It was spaces in the middle of the text. I'd already trimmed the left and right (there's actually a TRIM worksheet function). Substituting dashes for spaces:
Foo--Bar
It had to be changed to "Foo-Bar".
@DrPhil said:
I have extensive knowledge of Excel and use VLookup, Hlookup, vba, etc extensively and have yet to run across this issue you are describing. I tried simulating it on my end and found no problems with VLookup identifying items with multiple spaces within the table array. Without further clarification of your exact situation, I would assume it is user error, or missunderstanding of how the function works.
I've never run across it either. This was in Excel 2003, BTW. Replace the dashes in foo--bar with spaces, like the below table:
A B C D E 1 foo--bar =VLOOKUP(A1,D1,1,0) =VLOOKUP(A1,E1,1,0) foo--bar foo-bar
-
Interesting, I can't duplicate your issue. I tried 2007 SP1, 2003 initial release, and XP SP3. My gut tells me something is up with the source file or data though. Maybe what appeared to be a space actually wasn't. I had a issue like that about a month and a half ago.
-
Ok, so I am TRWTF. I used the worksheet function Trim within the VLOOKUP, and didn't realize that it would turn multiple spaces into single spaces.
-
@boomzilla said:
Ok, so I am TRWTF. I used the worksheet function Trim within the VLOOKUP, and didn't realize that it would turn multiple spaces into single spaces.
That's certainly a non-intuitive implementation of a trim function.
-
I'd say The Real WTF is the Trim function.