• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookups with concatenate / combining values, dates, and text

M

mikmayer

New Member
Hi all,


I have 3 unique keys that I have to combine into 1 grand key that I can then use to pull values from another sheet via vlookups. I have user ID (in number format), Current month (dd-mmm-yyyy date format) and I have an action description like #surveys or #projects (in text format)


When I combine the 3 using concatenate or =cell&..etc the output looks exactly like what is in the original table (first column of my vlookup range)...but for some reason it returns an incorrect value or gives me #N/A. I made sure there were no unnecessary delimiters / spaces, that all was locked properly... but still no dice. I also copy + pasted as values to avoid formula issues...


For example: I want the grand key to be 20#projects01-MAR-2012. In my lookup table the first column has the same tag in it, that I created through concatenate.

Why don't the two align? Why is it not returning a value even though they are exactly the same?


Any help would be appreciated. Thanks in advance!
 
Perhaps the date is causing problems? Concatenating it may be causing some issue with how XL interprets it (as date vs number vs text). For debugging purposes, I'd try manually finding two cells which should match and then do a simple comparison like

=A2=D2


If false, we can do a 1 character at a time comparison by doing:

=LEFT(A$2,ROW(A1))=LEFT(D$2,ROW(A1))

Copy that down, and when you see a false, you'll know which character is causing problems.
 
Thanks for the quick response. I identified a couple that are TRUE. So I retested again, knowing they should match, but the vlookup output value is still wrong. The value that should be returned is 2, but it is showing 0. It is really a simple vlookup, minus trying to combine the 3 different cells for my vlookup reference key... i'm stumped.
 
Hmm. The fact that it's giving a 0 instead of an error is interesting. Would you mind posting the exact formula you are using so we can check for any possible typoe? Would also help if you could post a sample of what the 3 columns look like and and it looks like in the original table.
 
Just figured it out (stupid oversight).. was on approximate match and not exact... which made the difference.


Thanks so much for your help though Luke!
 
Ha ha, glad it turned out to be something simple at least. Thanks for letting us know!
 
You must log in or register to reply here.
Back
Top