Forum Discussion

K1TK4T's avatar
K1TK4T
Copper Contributor
Feb 12, 2023
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...
excel
Excel for web
Formulas and Functions
office 365
  • Patrick2788's avatar
    Patrick2788
    Feb 14, 2023

    K1TK4T 

    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.

K1TK4T's avatar
K1TK4T
Copper Contributor
Feb 17, 2023
Apologies for the delay @Patric2788
Much appreciated, I certainly was missing some aspects within the formula! Spot on.
Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 17, 2023
You're welcome! I'm glad this solution fits your needs.

Resources