Looking up matching cell data based on a common ID

Question

Let me preface this by saying that I'm very much unfamiliar with Excel formulas. I only have a basic knowledge, and it has been years since I have used it at all.

I have two different sets of columns in my Excel worksheet, each having an ID and a name. Now I want to use a formula to generate a string that contains the matching cell values from both datasets, where the IDs match.

Since the second dataset doesn't necessarily have matching information for each item of the first dataset, I only want to generate the strings for each item of the second dataset.

Below is a screenshot of a simplified example detailing what I'm trying to achieve:

I have looked at VLOOKUP, INDEX, MATCH, but I'm unsure if or how I can get to the solution I want with those.

If anyone could help point me in the right direction, I would greatly appreciate it.

Answer 1

This should do what you want. if the records are the same in both table 1 and table 2, it returns Value unchanged, if the record exists only on table 2 but not on table 1, it returns Value not in original table. Let me know if you need to change this behavior.

=IFNA(IF(E2=INDEX($B$2:$B$7,MATCH(D2,$A$2:$A$7,0)),"Value unchanged", "Record "&D2&" changed from "&E2&" to "&INDEX($B$2:$B$7,MATCH(D2,$A$2:$A$7,0))),"Value not in original table")

Answer 2

This satisfies the brief as given, but assumes that if the ID exists it has changed name.

Formula is cell G2 is

=IF(ISERROR(MATCH(D2,$A$2:$A$6,0)),"","Record " & D2 & " changes from " & INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0))) & " to " & E2

Answer 3

I would add another column to set 2, which is a vlookup to see if the ID is found in set 1.

Formula for column F:

    Cell F2: =IFERROR(VLOOKUP(D2,A:B,2,FALSE),"not found")

Then, here's your formula for column G:

    Cell G2: =IF(F2="not found","Record "&D2&" not found in first set",IF(E2<>F2,"Record "&D2&" changes from "&F2&" to "&E2,"Record " &D2& " does not change"))