# Check for one value in column A exist in another column B in open office

suppose I have values like

A   B    C
abc cde
bcd abc
cde afg
mn   mn


for column C I have used formula =IF(MATCH(A1;B2:B4;0);A1)

But its giving wrong ouput like below

A   B   C
abc cde  #N/A
bcd abc  #N/A
cde afg  #N/A
mn    mn    mn


since abc and cde in column A exist in column B output should be something like this:

A   B   C
abc cde  abc
bcd abc  #N/A
cde afg  cde
mn    mn    mn


Any help is appreciated.

-

I have tried your (modified) formula. It works fine!

=IF(MATCH(A1;B$1:B$4;0);A1)


Result:

If you get #N/A errors, then it could be that the data in column A and column B are not exactly the same (hidden spaces). You could use =trim(A1) in C1 and =trim(B1) in D1 and copy it down, then run your formula in column E.

-
@Mohini, Please provide feedback on the proposed solutions. When an answer was helpful, please "Accept" that answer by clicking on the WHITE "Check-mark" next to the answer, to turn it GREEN. Thank you! Clicking the check-mark will give credits to the person who submitted the answer and will mark the question as answered. See: Accepting answers – Robert Ilbrink Mar 16 '13 at 8:18
Yeah I didn't put that $symbol. +1 From me-:) – Cindrella Mar 17 '13 at 3:46 Use the VLOOKUP call instead. In C2: =VLOOKUP(A2;B2:B4,1)  This means find A1 in range B2:B4 and return the first column with an exact match. - Thanks for suggestion(+1 from me). Its helpful. – Cindrella Mar 15 '13 at 13:18 I have used this formula: =IF(ISNA(VLOOKUP(A2;$B$2:$B\$718;1;0));A2;"")


output is

 A   B   C
abc cde
bcd abc  bcd
cde afg
mn   mn

-