+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    06-11-2015
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    4

    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. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-11-2015
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    4

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

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

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

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

  5. #5
    Registered User
    Join Date
    06-11-2015
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    4

    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. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #7
    Registered User
    Join Date
    06-11-2015
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    4

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

    Solved! thanks so much.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF statement with multiple true false variables
    By tommyt34uw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2014, 01:17 PM
  2. IF Stmt Returning a TRUE FALSE Value
    By J41 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-27-2014, 08:50 AM
  3. Returning True/False/Maybe
    By RivFin in forum Excel General
    Replies: 1
    Last Post: 02-03-2011, 06:29 PM
  4. Formula for returning True (1) or False(0)
    By wish2excel in forum Excel General
    Replies: 5
    Last Post: 10-11-2010, 04:14 AM
  5. Comparing 2 or more columns and returning a true value
    By stujordan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-10-2009, 08:46 AM

Bookmarks

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