CODEWITHSUNDEEP
excelexcel-formulavba
ggmkp
ggmkp

Reputation: 725

If Cell Contains XXX Then Return Corresponding Value

     A          B          C           D
1  Ross       Sales     John Guys    Finance 
2  Smith      Sales     Sam Andy     #N/A or False
3  Guys       Finance   Mike Ross    Sales

I'm putting this formula in cell "D1" but it's not giving me correct result

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A:A,C:C)))>0,B:B)

INDEX MATCH function would not work because it's not exact value

What can I put in "D1" and down to give me result as in the table above?

Upvotes: 1

Views: 1430

Answers (1)

user4039065
user4039065

Reputation:

Try this in D2,

=LOOKUP(1E+99, SEARCH(A2, C:C), B:B)
'or cut down the full column references
=LOOKUP(1E+99, SEARCH(A2, C$2:C$4), B$2:B$4)
'alternately as a wildcard MATCH
=INDEX(B:B, MATCH("*"&A2&"*",C:C, 0))

Fill down as necessary.

      LOOKUP_sEARCH

Upvotes: 2

Related Questions