# 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.

-

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 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

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
``````
-