CODEWITHSUNDEEP
excelexcel-formula
user270014
user270014

Reputation: 581

If column B cell value contains A cell value

I have two columns A and B in excel and I want third column output to be like show below

    A    B         C
    -------------------
    a |  sd      | a.com
    d |  a.com   |
    f |  g.in    |
    g |  ad      | g.in

B column has 'a.com'which contains 'a.' so C column it displays a.com .B column doesnt have which contains 'd.' so column cell is empty and so on..

Upvotes: 0

Views: 1085

Answers (2)

barry houdini
barry houdini

Reputation: 46451

Try this formula in C1 copied down

=IFERROR(VLOOKUP(A1&".*",B$1:B$4,1,0),"")

IFERROR function only works in Excel 2007 or later - for earlier excel versions try

=LOOKUP("zzz",IF({1,0},"",VLOOKUP(A1&".*",B$1:B$4,1,0)))

Upvotes: 1

Peter Albert
Peter Albert

Reputation: 17515

Put the following formula in column C:

=IFERROR(INDEX($B$1:$B$4,MATCH(A1,LEFT($B$1:$B$4,SEARCH(".",$B$1:$B$4)-1),0)),"")

It is an array formula, so press Ctrl-Shift-Enter instead of Enter when entering it.

Upvotes: 3

Related Questions