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.
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.
Thanks for replying Patrick2788
No problem.... bear with me while I mock-up a sample workbook for you to work from.
Appreciated.
No problem.... bear with me while I mock-up a sample workbook for you to work from.
Appreciated.
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.
- 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.- Yes, it can be done with Excel 2021 by adding some more logic to the formula. Are more pages going to be added in addition to those 3?