# Searching (looking up data) across multiple columns

#### LouiseWetherell

##### New Member
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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Alex Blakenburg

##### Well-known Member
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
ABCDEFGHIJKLM
1Output
2Search for -->aaa
3Using Found Helper column
4
5ProjectCol2Col3Col4Col5FoundProjectCol2Col3Col4Col5
6Item1aaahhhmmmuuuTRUEItem1aaahhhmmmuuu
7Item2bbbiiiooovvvFALSEItem3cccjjjjaaawww
8Item3cccjjjjaaawwwTRUE
9Item4dddkkklllyyyFALSE
10Item5eeelllsssxxxFALSE
11Item6fffnnntttzzzFALSE
12
Notes and workings
Cell Formulas
RangeFormula
H6:L7H6=FILTER(\$A\$6:\$E\$11,\$F\$6:\$F\$11,"")
F6:F11F6=IF(COUNTIFS(\$B6:\$E6,"*"&\$B\$2&"*")>0,TRUE,FALSE)
Dynamic array formulas.

#### LouiseWetherell

##### New Member
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
ABCDEFGHIJKLM
1Output
2Search for -->aaa
3Using Found Helper column
4
5ProjectCol2Col3Col4Col5FoundProjectCol2Col3Col4Col5
6Item1aaahhhmmmuuuTRUEItem1aaahhhmmmuuu
7Item2bbbiiiooovvvFALSEItem3cccjjjjaaawww
8Item3cccjjjjaaawwwTRUE
9Item4dddkkklllyyyFALSE
10Item5eeelllsssxxxFALSE
11Item6fffnnntttzzzFALSE
12
Notes and workings
Cell Formulas
RangeFormula
H6:L7H6=FILTER(\$A\$6:\$E\$11,\$F\$6:\$F\$11,"")
F6:F11F6=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.

#### Attachments

• 1616148107122.png
29.3 KB · Views: 0

Replies
0
Views
218
Replies
4
Views
142
Replies
9
Views
392
Replies
17
Views
310
Replies
5
Views
224

1,129,774
Messages
5,638,268
Members
417,019
Latest member
PKDP

### 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?

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