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.

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 13, 2023
I think this is do-able and it sounds like you've got a lot going on in that workbook. If you're able to share an anonymized workbook, it will help get you a solution (or two) very quickly.
K1TK4T's avatar
K1TK4T
Copper Contributor
Feb 13, 2023

Main Article has been updated with a file link :link: at the bottom.  I hope this helps.  I am still working on the main system and am planning on other mods; slow but sure.

Resources