Display match/cell contents if lookup contains specific text

[MrSamExcel]

What formula can match ID from ‘result’ tab with ID from ‘source’ tab and display the first cell in the same row of ‘source’ tab that matches partial/wildcard text string? Similar to a standard VLOOKUP or INDEX-MATCH however the value I’m looking for is not always in a single column but rather can appear anywhere in a range of columns.

Ex1) Enter formula on ‘result’ tab cell B2 that looks up ID from cell B1 from ‘source’ tab column A and returns the first cell in same row containing “*invest p*”? Formula would return “invest pro has X” from ‘source’ tab cell C2

Ex2) Same formula entered on ‘result’ tab cell B4 would produce value from ‘source’ tab E5: “invest pro has X”

Or even a filter/pivot method would suffice if it results in one column with ID and second column with "invest pro..." value.

Book3
	A	B
1	ID	RESULT
2	900066	invest pro has X
3	900054	invest pro has Y
4	900499	invest pro has X
result

Book3
	A	B	C	D	E
1	ID	Text1	Text2	Text3	Text4
2	900066	name1	invest pro has X	invest strat	other1
3	900054	name2	invest pro has Y	invest strat	other2
4	900073	name3	invest pro has Y	invest strat	other3
5	900499	name4	other	invest strat	invest pro has X
6	900057	name5	other	invest pro has Z	invest strat
source

 

[shift-del]

Hello MrSamExcel

Try this:

=VLOOKUP(A2,source!$A$2:$E$6,MAX(COLUMN(source!$C$2:$E$6)*MMULT(TRANSPOSE(--(source!$A$2:$A$6=A2)),--ISNUMBER(SEARCH("invest p",source!$C$2:$E$6)))),FALSE)

 

[MrSamExcel]

Yes, excellent, thank you! I'm noting for future reference that this formula may need to be array confirmed with CTRL-SHIFT-ENTER depending on your version of Excel.