Forum Discussion
Vlookup errors
Hi there,
in your scenario the VLOOKUP formula could look like this:
If there is a match, the column "Updated profile" will return the email address which was updated. If there is no match, formula will return 0.
Hope this helps!
Branka Kajiš, MCT
Thank you, Branka!
So incredibly kind for you to create an example for me.
I created your example in order to follow your formula and STILL receive the N/A error!
Could it be that my table array is too large (B3:B6431)?
Here is my view:
I have tried the formula with both semi-colons and commas between each function.
Excel is trying to tell me the error could be here:
Or within the table array?
When I replicated your example, it worked because my table array wasn't so large. I was only looking up info from $A$2:$B$9
Thank you for your advice!
Hi,
sorry for late response, somehow, I wasn't notified about your response.
As Hans already pointed out, it seems there is a space in column A, but not in your column E. Space is equal to any other sign or letter in Excel. It often causes issues when we compare the two cells. If you have A_ and A with space at the end, Excel considers it as two different contents.
You can check if this is the case, by comparing two same phone numbers - so take 1 from column A and compare it to the same phone number in the column E.
To compare the content of two cells, you can simply use = (equals) sign, as presented below:
In the first example there is space before phone number and when comparing it to the one in my column C, I get result “FALSE” - meaning the content in compared cells is not exactly the same.
So, there are many solutions to this. Two were already presented by Hans. In his solution he instructed VLOOKUP to search for space plus content of selected cell => =VLOOKUP(" "&E3, $A$3:$B$6431, 2, FALSE). Or in his other suggestion, you can look for »anything« which is indicated by * sign plust content of selected cell => =VLOOKUP("*"&E3, $A$3:$B$6431, 2, FALSE).
Alternatively, you could also use TRIM function, which cleans all unnecessary spaces from the cells. See below:
Let me know if you succeeded.
I GOT IT TO WORK! It had to do with the phone formating connected to the row I was trying to return the email. OMG. THANK YOU!!!!!!!!!!!!!!!!!!! You both stayed with me and I am so grateful. Big learning week with excel!
I have tried to respond and they are not being posted.
Unfortunately I did not have success with using the different formulas and trying to trim to get of the spaces. I think I need to download a clean version of the master data to try again. I have also tried to tag you BrankaKajis and Hans. I am not sure why they are not posting.
It looks like there is a space before the phone numbers in column A, but not in column E. Try
=VLOOKUP(" "&E3, $A$3:$B$6431, 2, FALSE)
or if that does not work
=VLOOKUP("*"&E3, $A$3:$B$6431, 2, FALSE)
Thank you for your response and attempt at trying to help me!
I did respond below to Branka. If you are unable to see my comment, please let me know.
Could you attach a sample workbook to a private message to me? Click on my user picture.
