VLOOKUP


  • ♿ (Parody)

    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.


  • ♿ (Parody)

    @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:


    ABCDE
    1foo--bar=VLOOKUP(A1,D1,1,0)=VLOOKUP(A1,E1,1,0)foo--barfoo-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.

     


  • ♿ (Parody)

     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.


Log in to reply