CODEWITHSUNDEEP
excelexcel-formula
Christopher
Christopher

Reputation: 620

How to cross check data in different Excel worksheets

How can I make sure that the postcode corresponding to a name in worksheet 1 matches the address for the same name in worksheet 2.

For example, I would need a column with a vlookup or index/match formula that would check Alice's postcode from worksheet 1 against Alices postcode in worksheet 2 and return true for a match of the postcode or false if the postcode is different.

Worksheet 1

Name   Address  DOB  Post code
Alice  Berlin   1980 W2
Bob    London   1979 EC1

Worksheet 2

Name  Address Post Code DOB
Alice London  EC        1979
Alice Berlin  W2        1980

I don't think a vlookup is ideal because of performance and column positioning.

Upvotes: 0

Views: 1281

Answers (2)

David Leal
David Leal

Reputation: 6759

I don't know if there is any other way to check that, other than VLOOKUP or MATCH function family. What I would recommend is to use the X-function family (XLOOKUP or XMATCH), this is what Microsoft recommends

For example:

=XLOOKUP(D3,$C$8:$C$9,$B$8:$B$9)=B3

Then drag down the formula.

Here is the output:

sample excel file

Upvotes: 1

Fred B
Fred B

Reputation: 1

Use a IF formula

For example in Sheet2 : =IF(B5=Sheet1!B5,"True"'"False")

Here is a link that might help

Upvotes: 0

Related Questions