Help Required - Formula combining CountIF with a Vlookup

[Rav_Singh]

I haven’t logged on here in around a year so clearly my Excel has improved but I have come up short in relation to my current Excel issue.

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

 

[Rav_Singh]

Just to provide an update, I located the formula below - which provides me the answer... However the limitation is when their are multiple matches for the text.

=VLOOKUP("*"&Value&"*",Tab!$RangeA$,column number,FALSE)

Is there a way to return all the values from (Tab 2, column E) which contain the text in Tab 1, column A.?

 

[AhoyNC]

What version of Excel are you using?
Maybe something like this:
Sheet1

Book1
	A	B	C
1
2	Employment and Pensions	2021	2022
3	Corporate Finance	3345
Sheet1

Cell Formulas
Range	Formula
B2:C3	B2	=IFERROR(INDEX(Sheet2!$E$2:$E$4,AGGREGATE(15,6,(ROW(Sheet2!$D$2:$D$4)-ROW(Sheet2!$D$2)+1)/(SEARCH($A2,Sheet2!$D$2:$D$4)),COLUMNS($B$1:B1))),"")

Sheet2

Book1
	D	E
1
2	Employment and Pensions Growth Forecast	2021
3	Corporate Finance and Diversity	3345
4	Employment and Pensions Growth Forecast	2022
Sheet2