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
Hello,
Thank you so much for your reply. It is greatly appreciated. I sent a message earlier letting you know that I was unable to get your example to work and I replicated yours exactly! I have removed all formating from the cells to keep trying.
I am getting this response.
My table array has 6431 records. When I have tried the formula by substituting the $B$9 for $B$6431 it also failed. I am sure I am doing that wrong!
I appreciate your logic!
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!
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.
