You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm not understanding why a vlookup is not working.
Data downloaded from 2 different systems.
Example
John Smith appears on both sets of data and formatted as text. However the vlookup is doing the following:
Returning #N/A
Unless I copy John Smith and paste as a value from the sheet being looked up into the sheet I want a value returned in.
Formula is:
=VLOOKUP(A2, 'Supplier Type_All'!$A$2:$B$804, 2, FALSE)
Interestingly, if I change from FALSE to TRUE, it returns a value but not the first one on the list associated with John Smith on the sheet I am looking up in.
Any thoughts?
Thanks
Any thoughts?
I suspect you might have answered your own question.
what look like identical text values.
Are they actually identical, or do they merely look identical to your eyes? If you manually type "John Smith" into one, the other or both of the two cells does it then work?
do both cells have the same format?
I've tried formatting cells as both general and text.
I do think it is because they look identical but are not. As above, I have copied and pasted the name from where I am looking up into the same name within where the lookup will take the required info back to and it then works.
Had this before with extra spaces etc. at the start or end of the data pasted in which aren’t visible in the sheet. Select a cell and then put your cursor in the formula bar where the data is and see if there is an extra space at the end?
RM.
I've had this..
I made a worksheet of source data. Duplicated this and added lookups that worked perfectly. I then duplicated this sheet (all the formatting and data was exactly the same as a result). The new lookup sheet one didn't work despite being a copy of the first and linking to the same source (as it was a copy).
If I copy the identical cell from the source data in to the new worksheet as the lookup criteria it works fine with no other changes.
Makes absolutely no sense. I'm assuming it's a bug.
Trim function is useful to get rid of errant spaces
If the data has come from an external database it often has extra space characters. The TRIM function is your friend here.
Tim 30 secs faster.
I think having multiple John Smith's in this instance is the issue.
Try lookup instead of vlookup?
Can you use index and match instead?
I always use index match. It’s far faster and more flexible.
for the op I get this and it’s usually because they aren’t the same format or one of them has the ‘ prefix as the first character which won’t be visible in the cells
I always use index match. It’s far faster and more flexible.
Not true. Depends on the scenario.
Select the column > select text to column > select fixed width
Click finish or ok whatever it says then do the same with the column from the other data
This might work if you have number formatted as text or something like that
Had this before with extra spaces etc. at the start or end of the data
This was the root of the problem when something similar plagued me in the past. It was a (relatively) small data set though so I could clean it up ok and the cleanup carried forward.
Trim function is useful to get rid of errant spaces
Though it seems this could have been better!
What jonnybike says, just because you change the format of a column doesn't mean the format has changed. If you do text to columns and remove all delimiters and choose general format it will make sure the format is actually applied.
It's the quicker version of clicking in each cell then pressing enter.
This is obviously assuming you don't have blank spaces at the end of the data