x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 263

Need formula example

In the attached spreadsheet, I seek to find duplicate ID numbers between Column A (List 1) and Column D (List 2). You will notice that the values of E3 and F3 (both have bad data) does not equal the values of B4 and C4, though they are associated with the same ID.  In column G (Matched), a formula drops in the duplicated ID numbers.

In this example, I want H4 and I4 to contain the (bad) data from E3 and F3, but only because the data is mismatched. If it all matched correctly, I would want the matched name and address to remain blank, like what you see with the matched ID 5 in G7.

What formula is required in H4 and I4?
Book1.xlsx
0
K_Deutsch
1 Solution

Commented:
Here are two formulas for cells H3 and I3:
=IF(\$G3="","",IF(\$B3=VLOOKUP(\$A3,\$D:\$F,2,FALSE),"",VLOOKUP(\$A3,\$D:\$F,2,FALSE)))
=IF(\$G3="","",IF(\$C3=VLOOKUP(\$A3,\$D:\$F,3,FALSE),"",VLOOKUP(\$A3,\$D:\$F,3,FALSE)))

If G3 displays a blank, the formulas return a blank. If G3 displays a number but the corresponding data in B3 doesn't match column E, the first formula returns the mismatched value from column E. Otherwise, it returns an empty string.
0

Author Commented:
Perfect, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.