Compare columns and return X if

[dragonmouse]

I want to compare multiple columns with serial numbers (same work sheet):

If column "A" matches a number in column "B" return RCVD OR
If Column "A" matches a number in Column "C" return INDUCT OR
if Column "A" matches a number in column D return DONE OR
IF Column "A" does not match ANY column return "BLANK"

Thoughts? NOTE: The ROWS will not be lined up. If they were it would be easy.

 

[jasonb75]

Maybe

=IFERROR(CHOOSE(MATCH(A2,B2:D2,0),"RCVD","INDUCT","DONE"),"")

 

[dragonmouse]

Ok that's VERY close however it's only comparing the data on that ONE row B2:D2 rather than matching cell A2 with the entire column

 

[jasonb75]

In that case, try

=IFERROR(CHOOSE(MATCH(A2,FILTER($B$2:$D$20,($B$2:$B$20=A1)+($C$2:$C$20=A1)+($D$2:$D$20=A1)),0),"RCVD","INDUCT","DONE"),"")

 

[dragonmouse]

It does not like "FILTER".

 

[jasonb75]

Please click the link below to update your profile with your office version, then click the Save button at the bottom.

https://www.mrexcel.com/board/account/account-details

 

[dragonmouse]

Updated Profile. Office 2016

 

[dragonmouse]

Done updated profile to show Office 2016

 

[jasonb75]

Going with the simplest method

=if(countif($B$2:$B$20,A1),"RCVD",if(countif($C$2:$C$20,A1),"INDUCT",if(countif($D$2:$D$20,A1),"DONE","")))

If the content of A2 is found in more than 1 column then the first column it is found in will take priority.

 

[dragonmouse]

AND WE HAVE A WINNER. WORKS! Thank you!