Excel 2007 : Need to Compare three columns for matching values and populate a predetermined Value.

[jinsi.george]

I have three columns A, B and C and need to compare the text values in each cell in column A exists/matches to any of the cells in column B and column C. If it matches to column B and C I need to populate a value 2 in a new column D else I need to populate a value 1 in column D if it matches to only one of the column and a value 0 if it does not have a match in either of the columns. I tried using a vlook up and a match condition but to no avail. Any help on this regard would be highly appreciated.

[tigeravatar]

jinsi.george,

Welcome to the forum!
In cell D1 and copied down:
=CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,0,1,1,2)

Same formula, simplified:
=OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))

[Søren Larsen]

If column A values can only match ones in each of columns B and C, then you can use =Sumproduct((A1=B:B)*1;(A1=C:C)*1) and copy down.

[jinsi.george]

jinsi.george,

Welcome to the forum!
In cell D1 and copied down:
=CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,0,1,1,2)

Same formula, simplified:
=OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))

__________________________________________________________________

Thanks for the solution. Though I have one more concern. If I want to depict these predetermined values with a text value For eg. matching to Column B would show up as B, matching to column C would need to show up as C and matching to both as D. what options could I use?

[tigeravatar]

jinsi.george,

Use the first formula I posted, except change the last part of it to what you want to display:
=CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,"Neither","B","C","D")