Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.