Compare and Update values in multiple columns

Question

Using Excel 2013 and need to figure out how to compare and update values. If anyone is able to help, I would greatly appreciate it!

In column A, I have a new list of 6000+ user names.

In column B, I have the new phone number associated with each user account.

In column D, I have an old list of 6000+ user names.

In column E, I have the old phone number associated with each user account.

I need help in figuring out how to compare the data in column A and Column D. If they match, I then need to have the value in Column E updated to match the value in Column B.

Any help is greatly appreciated!

Answer 1

The following actually did exactly what I needed it to do-

Put the following in column F and drag it down and then copy the data over

=IFERROR(IF(MATCH(D1,$A:$A, 0), INDEX($A:$B, MATCH(D1,$A:$A, 0), 2), E1), "")

Hi,

That's a more complicated way of doing what the formula I gave you does. The one I gave you is the way to go. I deleted the no match message to return an empty cell.

=IFERROR(VLOOKUP(D2,$A$2:$B$6000,2,FALSE),"")

Answer 2

The following actually did exactly what I needed it to do-

Put the following in column F and drag it down and then copy the data over

=IFERROR(IF(MATCH(D1,$A:$A, 0), INDEX($A:$B, MATCH(D1,$A:$A, 0), 2), E1), "")

Answer 3

Hi Mike,

What I am needing to do is to compare column A and E and if they match, I want the value in Column F to match the value in Column B. The data in Column B contains the updated numbers (from 4 digits to 10 digits) so I want Column E to be updated to match if there is a match in the user name.

Hi,

You have data in column E so a formula cannot change the data in that column. The formula I gave you; here in Col F, would check each name in Col D and return the new number from Col B.

If you want to change Col E then you need VB code.

User name	New No		User Name	Old No	Formula
aaa	123		bbb	678	678
bbb	678		aaa	456	123
ggg	876		ggg	876	876
qwe	12345		qwe	45678	12345
wer	21		poiu	321	No Match

Answer 4

Hi Mike,

What I am needing to do is to compare column A and E and if they match, I want the value in Column F to match the value in Column B. The data in Column B contains the updated numbers (from 4 digits to 10 digits) so I want Column E to be updated to match if there is a match in the user name.

Answer 5

Hi,

One way is with a formula in a helper column to lookup the new number.

=IFERROR(VLOOKUP(D2,$A$2:$B$7000,2,FALSE),"No Match")

This can then be copied and pasted over column E as values but be aware that if there's no match found the formula returns 'No Match' so if you pasted you would lose the old number too.