# Comparing Multiple columns across worksheets and returning a True/False statement

1. ## Comparing Multiple columns across worksheets and returning a True/False statement

Hi All,

I have tried many functions to get this to work however I can not seem to crack my problem, hoping someone can help out!

Basically what I am trying to do is compare the data from Sheet 1 both column A and B to see if the data in Sheet 2 A and B match up. Then return TRUE or FALSE (1 or 0) if both columns in each sheet match up.

I have tried these two formulas that just return the same on everything. =ISNA(MATCH(A1:B1,Sheet1!A:B,0)) and =IFERROR(IF(A1:B1,MATCH(Sheet1!A:B,0),"yes"),"no")

Sheet 1
A B
Type Location TRUE/FALSE?
Router Australia
Switch NZ
Switch Australia

Sheet 2
A B
Type Location
Router Australia
Switch NZ
Switch Australia

Thanks in advance for the assistance.

2. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

Try this...

Entered in C2 and copied down:

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),TRUE)

3. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

That worked a charm. Just saved me hours thank you!

4. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

5. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

Actually one more question has popped into mind sorry.

is there any way to add to the formula to check for extra data in a field some of the columns should be the same yet have a V01 V03 at the end.

So if sheet 1 has this

Location Type
Australia Router

And Sheet 2 has
Location Type
Australia Router V03

Is there a way to still return true on column C even with the extra V03 tacked on the end?

6. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

Maybe something like this...

=IF(SUMPRODUCT(--(Sheet2!A\$2:A\$10=A2),--(LEFT(Sheet2!B\$2:B\$10,LEN(B2))=B2)),TRUE)

7. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

Solved! thanks so much.

8. ## Re: Comparing Multiple columns across worksheets and returning a True/False statement

Good deal. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1