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.

  • K1TK4T's avatar
    K1TK4T
    Copper Contributor
    Feb 13, 2023
    Thanks for replying Patrick2788
    No problem.... bear with me while I mock-up a sample workbook for you to work from.

    Appreciated.
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Feb 14, 2023

      K1TK4T 

      I reviewed your workbook. Essentially, you're looking to expand the INDEX to be able to return values from 1 of 3 sheets.

       

      INDEX has a reference form which can take multiple ranges or named items, but those areas must be on the same sheet.

       

      A basic sample using defined items 'one' and 'two'.  If these two are not on the same sheet, the result will be a #VALUE error.

      =INDEX((one,two),1,1,2)

       

      My recommendation is to consolidate the Page 1-3 sheets to simplify the INDEX.  If you were on Excel 365, I'd stack the arrays and do the lookup but 2021 does not have access to stacking functions.

       

      • K1TK4T's avatar
        K1TK4T
        Copper Contributor
        Feb 14, 2023
        How do Patrick2788

        I greatly appreciate your time and feedback... The unfortunate aspect of the project is that the pages cannot be merged due to a conflict in data types, etc. While I do also appreciate it would be easier, the current method does proves it can be done in both 2021 and 365.

        That said, I would use the combine method if I could find a solution that also merges data without removing duplicates, as found within the matrix page.

        If anyone else, or you can assist within the outlines set, I look forward to your help.

Resources