In short I require a formula to (1) identify where text from one cell is contained in another cell (in a longer string) (2) If there is a match then do a Vlookup formula to extract data from a corresponding column.

Example scenario below;

Tab 1:

Cell A2: Employment and Pensions

Cell A3: Corporate Finance

Tab 2:

*Column D houses the range I wish to check to see if text from Tab 1, Cell A2 is contained in a cell in this column*

Cell D2: Employment and Pensions Growth Forecast

Cell E2: 2021

Cell D3: Corporate Finance and Diversity

Cell E3: 3345

What I would love to have is a formula that provides the following solution;

Tab1:

Cell B2: 2021 (as 'Employment and Pensions' is contained in Tab2, cell D2)

Cell B3: 3345 (as 'Corporate Finance' is contained in Tab2, cell D3)

What I have done so far is run the following formula;

=COUNTIF(rng,"*"&value&"*")>0

=COUNTIF(Tab2!B:B,"*"&'Tab1'!A2&"*")>0

This formula results in either a True/False result, which allows me to fulfil part (1) of the task (to identify if text in Tab1, column A is contained in a cell in tab 2, column D).

However I am unable to progress this formula to run a further query to say that if there is a match, then provide data from Tab 2, corresponding cell in column E.

I hope this makes sense, unfortunately I have exhausted my online research to come up with a solution, so am gratefully looking for any assistance on here.

Many thanks for any solutions provided in advance.

Rav