LouiseWetherell
New Member
- Joined
- Mar 17, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- 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.
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.
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.