Lookup value

[hiepkim]

Hi Everyone,

Hope someone can help please

Column A

Marketing ABC
DCF Marketing
Sales DEF
HGF Sales

What I want is to look up column A and if it contains any value in Column C then return value in D

Column C------------Column D

Marketing------------Marketing Team
Sales-----------------Sales Team

So the result will look like this

Column A-------------Column B

Marketing ABC-------Marketing Team
DCF Marketing-------Marketing Team
Sales DEF------------Sales Team
HGF Sales------------Sales Team

It has to look for data in the table rather than multiple if functions otherwise it will be a very long if functions . Any suggestion is much appreciated.

Thanks

Kim

[jeffreybrown]

Hello & Welcome to the Forum,

A vlookup should work for you >> =VLOOKUP(A1,$C$1:$D$1,2,0)

https://www.contextures.com/xlFunctions02.html

[hiepkim]

Hi Jeff,

Thanks for the reply. No it doesn't since value in column A is not the same as value in column C. Cheers

[Sam Capricci]

Can you set up a lookup table for the list or are there too many?

[hiepkim]

Hi Sam, unfortunately it is not possible since each month it will be different. e.g "Marketing for Jun20" so just need to capture the main word in the text

[FDibbins]

to avoid wasting your time and those helping you I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like. (see yellow banner above)

[hiepkim]

Hi Ford,

Examples uploaded.

Thanks

Kim

[AliGW]

In F2 copied down:

=VLOOKUP(E2&"*",$A$2:$B$13,2,0)

[hiepkim]

Hi Ali,

Thanks for the response but I am after the value in B column (expected result). E:F is look up table.

Thanks

Kim

[AliGW]

Oops! Try this:

=IFERROR(LOOKUP(1000,SEARCH($E$2:$E$3,A2),$F$2:$F$3),"")

[hiepkim]

Hi Ali,

Work like a charm. Thanks heaps.

BTW, what is the 1000 in lookup is for? Thanks

[FDibbins]

Just a value that is larger than anything you might have in your table.

[hiepkim]

Thank you for all your help