Match entries in multiple columns

**clash005** · 12-03-2009, 09:57 AM

Hi all,

Please see the attached, I have tried (unsucessfully) to cross match three columns B,E & G to see if the entry in B is in the other two and to give a answer in column C. But I can't seem to get it right, can anyone please help a weary old so and so.

Cheers

**jasoncw** · 12-03-2009, 10:05 AM

I can't see the workbook (Excel 2003 at work w/o the 2007 conversion

), but see if this will work for you (gives TRUE/FALSE response):

=AND(COUNTIF(E:E,B1)>0,COUNTIF(G:G,B1)>0)

HTH,
Jason

**NBVC** · 12-03-2009, 10:05 AM

I like your formula... but when both match, you always get 2...

Try amending to:

=LOOKUP(IF(ISNUMBER(MATCH($B3,$E$3:$E$21,0)),1,0)+IF((ISNUMBER(MATCH($B3,$G$3:$G$76,0))),2,0),{0,1,2,3},{"Not Found","RA NO","RA Yes","Both"})

copied down.

**clash005** · 12-03-2009, 10:09 AM

NBVC, Thanks very much, I need all of the hair I've got at my age.

**NBVC** · 12-03-2009, 10:28 AM

I guessed that formula came from DonkeyOte or Daddylonglegs.. so I looked back at your previous posts...

Although my rendition works too, I added an IF statement, but DonkeyOte avoided that, smartly, just by multiplying the second part of the lookup by 2...

So all you forgot was the 2* on the second part...

e.g. Should be

=LOOKUP(ISNUMBER(MATCH($B3,$E$3:$E$21,0))+2*(ISNUMBER(MATCH($B3,$G$3:$G$76,0))),{0,1,2 ,3},{"Not Found","RA NO","RA Yes","Both"})