function to match values in 3 columns

**brichigo** · 05-10-2012, 10:58 AM

Hi Excel Experts,

I am trying to match a value from column 1 and check if it exist in col 2 and col 3.

If yes, then output the value(col 1) in the 4th column or just display "true"

Sample:
col1 col2 col3 col4
1 0 2 1 or true <- true because there is "1" in col 2 and col 3
2 0 1
3 1 0
4 3 5
5 5 6 5 or true

Thanks in advance for the help!!

**GeneralDisarray** · 05-10-2012, 11:02 AM

Assuming col1 = A, col2=B, col3=C...Then you would put this in the 4th column and copy/paste down.
=OR(A1=B1,A1=C1)

adjust to fit your ranges

**Fotis1991** · 05-10-2012, 11:08 AM

Hi

Try,

=if(or(a1=b1;a1=c1);a1;"")

edit: Change the semi colons to comma, if you have to do this.

**rvasquez** · 05-10-2012, 11:12 AM

Hello the below formula should work for you

Please Login or Register  to view this content.

Thanks,

RVASQUEZ

**brichigo** · 05-10-2012, 11:19 AM

Hi All!

Thanks for the quick response.

My goal is to check if the value in col A is also found in both col B AND col C in any order.

I have attached a sample workbook. thanks much!

**Cutter** · 05-10-2012, 11:51 AM

Try
=AND(ISNUMBER(MATCH(A2,B:B,0)),ISNUMBER(MATCH(A2,C:C,0)))

**brichigo** · 05-10-2012, 12:01 PM

Originally Posted by Cutter

Try
=AND(ISNUMBER(MATCH(A2,B:B,0)),ISNUMBER(MATCH(A2,C:C,0)))

Hi Cutter,

This is perfect! Thank you!!

Thank you all! I really appreciate your help!

**Cutter** · 05-10-2012, 12:13 PM

You're welcome. Thanks for the 'star tap'. Don't forget to mark your thread as SOLVED (click on Forum Rules at top of page for instructions if needed).

**brichigo** · 05-10-2012, 09:35 PM

Hi Cutter/All,

What if im looking for a common value that exist in 3 columns in any order?

Then output that common value in the 4th column.
Thanks again!

**GeneralDisarray** · 05-11-2012, 12:04 PM

hmmm...how about the following choose statement:

=CHOOSE(1+1*(A1=B1)+2*(B1=C1)+3*(C1=A1),"",A1,B1,C1,"","",A1)

Looks like it works. The cases (1-7) happen in the following situations:

1 = No common values
2 = A,B common only
3 = B,C common only
4 = A,C common only
7 = A,B,C common
5 = can't happen
6 = can't happen

function to match values in 3 columns

LinkBack

Thread Tools

Rate This Thread

Display

function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Re: function to match values in 3 columns

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions