How can I return a value in one column from another sheet based on two inconsistent string criteria?

olsxn

Hi, any help with this will be hugely appreciated.

On my master sheet I have a "Task Name" column, an "Audience" column and a "Description" column I want to populate with this formula.

On the source sheet I have a "Task Name" column, an "Audience" column and the "Description" column I want the data from.

Unfortunately, the text strings in "Task Name" and "Audience" will not always exactly match across the two sheets but the strings will definitely be there in some form, e.g. "US users" in the source sheet will be "US" in the master sheet.

I have tried VLOOKUP with multiple criteria but due to the slightly different strings it didn't work. Is there a way I can nest functions within the VLOOKUP to get the data from the "Description" column?

Another problem with this US example is that the word Russia also contains US.

I want it to be a column formula also, as editing the formula for each cell isn't possible due to the size of the sheet.

Please let me know if you need any other information. Thank you

Paul Newcome

Are you able to provide some screenshots for context?

olsxn

Hi @Paul Newcome , apologies for the delay.

I've included some screenshots of some dummy data. As you can see the tasks may be related to the same thing but slightly differently worded. I guess this would require a different formula for each one so it's not really efficient, right?

I think maybe my best course of action here is to assign a unique Task ID to each task, in this example Row 2 on each sheet would have the same Task ID, and use that in a VLOOKUP with a single criteria. Does this sound like a more straightforward way of going about this?

Thanks a lot again