Comparing 2 columns

[AlgaRejov]

Hi guys,

I'm fairly new to this so please bear with me. I know this is something that was posted before but most of the answers I've found so far, compare items within the same row and what I need, is to compare i.e B4 to ALL of column A and return a value in column C. Also, column B is shorter than Column A.

Example:

Column A

Red
Green
Blue
Blue
Blue
Yellow
Brown
Pink
Black
Purple
Green

Column B

Pink
Blue
Green
Black
White
Yellow

The outcomes:

1) So, if 'Blue' from column B is found anywhere on Column A (even if it's more than once), I want a value returned on Column C (next to Blue, so C2) that says 'OK'.

2) If there is a value on Column B that doesn't exist on Column A (i.e 'White) I need a value of 'Missing' returned on column C.

I hope this is not too complicated. Could someone give me a hand with this?

THANK YOU.

G.

 

[VoG]

Try this

Excel Workbook
	A	B	C
1	Red	Pink	Missing
2	Green	Blue	OK
3	Blue	Green	OK
4	Blue	Black	OK
5	Blue	White	OK
6	Yellow	Yellow	OK
7	Brown		Missing
8	Pink		OK
9	Black		OK
10	Purple		Missing
11	Green		Missing
Sheet3

 

[AlgaRejov]

Thanks Peter! Though C1 should return 'OK' and C5 should return 'MISSING', as there is no match for 'white', right?


Thank you.

G.

 

[VoG]

Sorry, I had it the wrong way round

=IF(ISNUMBER(MATCH(B1,A:A,0)),"OK","Missing")

 

[AlgaRejov]

Yes! That's perfect.. THANK YOU!