Forum Discussion
Solved
Index and match for a single output from data across multiple pages
Hi All, I'm a bit of a novice when extending out from the normal set of formula you can use in excel. I have a project under way being built under Office 2021 - to then be migrated to SharePoint 365...
Here's the formula for the Group. The Region formula is nearly the same. The formula uses dynamic named ranges.
=LET(k,IF(ISNUMBER(MATCH(B3,P1User,0)),1,IF(ISNUMBER(MATCH(B3,P2User,0)),2,IF(ISNUMBER(MATCH(B3,P3User,0)),3,4))),CHOOSE(k,INDEX(P1Group,MATCH(B3,P1User,0)),INDEX(P2Group,MATCH(B3,P2User,0)),INDEX(P3Group,MATCH(B3,P3User,0)),""))Please see attached workbook.
Yes, 365 does appear limited, so all developments will be completed offline.
As we speak, not that I'm aware of... If needed, embedd the adjustment in the relevant sheet as general text, I'll download and encode properly.
As we speak, not that I'm aware of... If needed, embedd the adjustment in the relevant sheet as general text, I'll download and encode properly.
Here's the formula for the Group. The Region formula is nearly the same. The formula uses dynamic named ranges.
=LET(k,IF(ISNUMBER(MATCH(B3,P1User,0)),1,IF(ISNUMBER(MATCH(B3,P2User,0)),2,IF(ISNUMBER(MATCH(B3,P3User,0)),3,4))),CHOOSE(k,INDEX(P1Group,MATCH(B3,P1User,0)),INDEX(P2Group,MATCH(B3,P2User,0)),INDEX(P3Group,MATCH(B3,P3User,0)),""))
Please see attached workbook.
- You're welcome! I'm glad this solution fits your needs.
- Apologies for the delay @Patric2788
Much appreciated, I certainly was missing some aspects within the formula! Spot on.