Find based on partial text to another tab to get answer?

[sininv]

Hi


Need help I am trying to find the CL in tab 1 to pull out from the table in Tab 2

eg: Senior Vice President in tab 1 - should auto pick up from Tab 2 as CL4

Can please advise the formula. Thank you

 

[sininv]

hi Joe,

thanks for the help. Maybe if you can advise what is the best way to set the Datasource table so that it can pick? Not very sure how .

yes, the formula I try , it doesnt not pick up most of it.

I just wonder, I was googling.. is it possible to use index, match to partial match the text to the data source? not sure how to. Thank you

It's so hard to match and find back the right data.

 

[Joe4]

A partial match of any kind is going to be hard because you have so many similar entries (i.e. the words "Director" and "Vice President" appear under multiple entries).
It is so hard to program something that will always work when you have so much similar data, and inconsistency in the data. It would be very difficult to come up with a solution that is 100% accurate.

If it is not possible to clean-up the data you get (i.e. get them to send more standard values that match your expected values), I think I would be more inclined to create a lookup table that contains EVERY possibility, and what you want to return, and then use a VLOOKUP formula, and then keep adding new entries to that table as you find new records with no match.