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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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,720
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,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top