Improve company productivity with a Business Account.Sign Up

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

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
Asked:
K_Deutsch
1 Solution
 
byundtCommented:
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
 
K_DeutschAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now