1

I have four columns Name, Y/N, NameList and Result, e.g.: 

   A          B              C            D 
  Name       Y/N          NameList      Result   
  Abc         Y            Xyz            N
  Xyz         N            Wto            N.A
  Def         Y            Abc            Y
                           Tow            N.A
                           Wtf            N.A
                           Qrz            N.A
                           Def            Y

I want to fill up column D (Result) according to column B if A and C match. I have tried LOOKUP, VLOOKUP and MATCH but still do not get what I want, e.g.: 

=INDEX($B$2:$B$51,MATCH($A$2:$A$51,$C$2:$C$75,0))

What am I doing wrong here?

Improve this question

2 Answers 2

Reset to default
1

If you are prepared to replace the spaces in ColumnA (with nothing) then 

=IFERROR(VLOOKUP(REPLACE(C2,SEARCH(" ",C2),1,""),A$2:B$5,2,FALSE),VLOOKUP(C2,A$2:B$5,2,FALSE))

should work for entries in the NameList that include a single space, as well as those with no spaces but you might want to apply TRIM to NameList first.

NOTE: Chris Neilsen's solution (in a comment on the OP's own answer) is a much better solution (once the requirements were clarified!)

Improve this answer
3
  • may i uds what does the 1 stands for in the search function?
    – 10e5x
    Commented Dec 28, 2012 at 8:46
  • I tried your solution but cant work. For e.g I want to match APL DEN/055W (column A) with APL DEN /055W (Column C), JAME RIVER B/018A with JAME RIVER B /018A. And do i need the IFERROR? i need this kinda urgent, any help?
    – 10e5x
    Commented Dec 28, 2012 at 9:03
  • I am thinking to make my column A from "/" to " /". But i dk how to implement the replace on column A unless i generate out another column
    – 10e5x
    Commented Dec 28, 2012 at 9:16
0

I have found solution to my own answer but it is not the perfect one because it needs to match exactly the same. Meaning the spaces. But i will stick to this first. If anyone has better answer please do advise. 

    =VLOOKUP(C2,$A$2:$B$4,2,FALSE)

Thanks

P.S what should i do to also match abc/123 and abc /123. Due to the space currently they are not match

Improve this answer
2
  • To handle the possibility of spaces in column A, use =VLOOKUP(C2,SUBSTITUTE($A$2:$B$4," ",""),2,FALSE) entered as an array formula (press Ctrl-Shift-Enter rather than just Enter to finalise the formula)
    – chris neilsen
    Commented Dec 28, 2012 at 9:45
  • 1
    @pnuts If Column C might also contain spaces use =VLOOKUP(SUBSTITUTE(C2," ",""),SUBSTITUTE($A$1:$B$4," ",""),2,0) (also as an array formula)
    – chris neilsen
    Commented Dec 28, 2012 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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