Help Required - Formula combining CountIF with a Vlookup

Rav_Singh

New Member
Joined
Jun 29, 2019
Messages
28
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Rav_Singh

New Member
Joined
Jun 29, 2019
Messages
28
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,682
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Maybe something like this:
Sheet1
Book1
ABC
1
2Employment and Pensions20212022
3Corporate Finance3345 
Sheet1
Cell Formulas
RangeFormula
B2:C3B2=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
DE
1
2Employment and Pensions Growth Forecast2021
3Corporate Finance and Diversity3345
4Employment and Pensions Growth Forecast2022
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,118,270
Messages
5,571,238
Members
412,372
Latest member
JON_ROCKS
Top