Searching (looking up data) across multiple columns

[LouiseWetherell]

I am trying to lookup a value, that may be over multiple columns, or XLOOKUP but across multiple columns, which I've found out can't be done. I want to search many columns and bring back one piece of data, not many. I have tried multiple lookup formulas as well as INDEX and MATCH and can't really get anything to work so would be amazing if someone could help me.

I have a resource document where I have one row per project and then each role has a drop down of colleagues name, and then I need to lookup which colleague is working on which project in a new sheet. However each colleague can sit under more than one role so it needs to search L:BD rather than L:L, as below.

I have tried INDEX MATCH with this formula and it works with L:L but not L:BD. =INDEX('MASTER DATA'!B:B, MATCH(A3, 'MASTER DATA'!L:L, 0))
Also I have tried XLOOKUP for looking in L:L but again it won't search the whole L:BD area. =XLOOKUP(A3,'MASTER DATA'!L:L,'MASTER DATA'!B:B)

Any help anyone can give me would be much appreciated, thanks so much! Louise.

 

[Alex Blakenburg]

Are you prepared to use a helper column ?
The Found column looks for the search criteria eg colleagues name, and returns TRUE/FALSE.
You have office 365 so the filter function filters on TRUE and returns the required rows (and columns)

20210318 Multi column search.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								Output
2	Search for -->	aaa
3								Using Found Helper column
4
5	Project	Col2	Col3	Col4	Col5	Found		Project	Col2	Col3	Col4	Col5
6	Item1	aaa	hhh	mmm	uuu	TRUE		Item1	aaa	hhh	mmm	uuu
7	Item2	bbb	iii	ooo	vvv	FALSE		Item3	ccc	jjjj	aaa	www
8	Item3	ccc	jjjj	aaa	www	TRUE
9	Item4	ddd	kkk	lll	yyy	FALSE
10	Item5	eee	lll	sss	xxx	FALSE
11	Item6	fff	nnn	ttt	zzz	FALSE
12
Notes and workings

Cell Formulas
Range	Formula
H6:L7	H6	=FILTER($A$6:$E$11,$F$6:$F$11,"")
F6:F11	F6	=IF(COUNTIFS($B6:$E6,"*"&$B$2&"*")>0,TRUE,FALSE)
Dynamic array formulas.

 

[LouiseWetherell]

Are you prepared to use a helper column ?
The Found column looks for the search criteria eg colleagues name, and returns TRUE/FALSE.
You have office 365 so the filter function filters on TRUE and returns the required rows (and columns)

20210318 Multi column search.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								Output
2	Search for -->	aaa
3								Using Found Helper column
4
5	Project	Col2	Col3	Col4	Col5	Found		Project	Col2	Col3	Col4	Col5
6	Item1	aaa	hhh	mmm	uuu	TRUE		Item1	aaa	hhh	mmm	uuu
7	Item2	bbb	iii	ooo	vvv	FALSE		Item3	ccc	jjjj	aaa	www
8	Item3	ccc	jjjj	aaa	www	TRUE
9	Item4	ddd	kkk	lll	yyy	FALSE
10	Item5	eee	lll	sss	xxx	FALSE
11	Item6	fff	nnn	ttt	zzz	FALSE
12
Notes and workings

Cell Formulas
Range	Formula
H6:L7	H6	=FILTER($A$6:$E$11,$F$6:$F$11,"")
F6:F11	F6	=IF(COUNTIFS($B6:$E6,"*"&$B$2&"*")>0,TRUE,FALSE)
Dynamic array formulas.

This looks like it may work, thank you so much, I will have a play around and let you know if its what I need.