Searching (looking up data) across multiple columns

LouiseWetherell

New Member
Joined
Mar 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
1615997090345.png

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
562
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
    1616148107122.png
    29.3 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,029
Members
416,892
Latest member
Bensch

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