Forum Discussion
Vlookup errors
Hello.
7.15 - Sent 4k SMS text messages via mailchimp via an excel import
7.22 - I want to cross reference the master list of phone and emails (6K) to see who updated their email per the SMS request
I have 2 workbooks in one file.
Workbook 1 - MASTER LIST
phone and email columns
Workbook 2 - LIST OF PHONE #S SENT SMS
1. phone column only
2. need to add another column for emails to populate into
I have copied both workbooks and re-pasted as values.
I want to pull email data from W1 that matches phone in W2 in order to see who updated their profile.
WHY CAN I NOT GET MY FORMULAS TO WORK? I have no idea what I am doing wrong.
14 Replies
I have no idea what is going on with Excel Vlookup and this feed! My responses are not being posted. Before I write my boo hoo story again, I want to be sure this message is seen by my Excel mentors!
No success with your provided formulas. It has to be something with my spreadsheet. I've removed all formating, I've TRIM'd both phone columns, etc.....
I am sorry you are not receiving my SOS response messages! Maybe they have to vetted by MS before posted?I love a challenge and will not stop until I know WHY it is not working because I need to learn the why. My response to both of your messages may have been flagged because I said I wish I could just share screens to see WHY!
Hello my Excel friends.
Here we go another day.....without success!
- I formated columns A and E as 'special - phone number'
2. I did a full copy of the worksheet and re-pasted as values
3. When I replicate(d) your formula(s) I receive a #N/A error output
Here is the other formula with the same result. I did received a '!' alerting me to an error in my formula.
Regarding the TRIM option
I was able to do this for column A, where the space was before the phone number. When I did this it removed the () from number as well. When I tried to TRIM the phone numbers in column E it didn't remove the (). I have tried to format both the same in order to align in each column.
- I wish I could attach the file I am working with as you may be able to find errors I am not seeing in the formatting.
- I am doing something wrong and trying to find out what that is will cause me to throw my computer out of the window!
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.
