Lookup a Match in an Array that can be in any column of the Array

[rehanahmad]

Hi
I am working on an Excel model where I am struggling to find a formula where I want to look for an exact match in a table of array that can be found in any columns out of 42 columns of the array with about 4000 rows. The search criteria I want to lookup can have up to 2 matches. Can someone please help?

 

[Peter_SSs]

"COLUMNS($B:B))", is it referring to the Column B where the formula is written? i.e. if the formula is written in B2 in your example, it this part "COLUMNS($B:B))" referring to the same column?

Yes, the COLUMNS function should contain the column of the first cell with the formula before it is copied to the right. So if the first formula column was column D like below, then that first formula contains COLUMNS($D:D)

rehanahmad.xlsm
	A	B	C	D	E	F
1	Lookup String			Result 1	Result 2	Result 3
2	W4			B	C
3	A6
4	Y9			A	B	D
5	K9
6	J5			D
7	O3			D
Sheet2

Cell Formulas
Range	Formula
D2:F7	D2	=IFERROR(INDEX(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,INDEX(Sheet1!$A:$A,IF(Sheet1!$T$2:$AB$5=$A2,ROW(Sheet1!$A$2:$A$5),99))&"")&"</c></p>","//c"),COLUMNS($D:D)),"")