Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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

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

Result:

enter image description here

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.

share|improve this answer
    
@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.

share|improve this answer
    
Thanks for suggestion(+1 from me). Its helpful. – Cindrella Mar 15 '13 at 13:18
up vote 0 down vote accepted

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    
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .