Index and match for a single output from data across multiple pages

[K1TK4T]

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.

As I have typed out the title and checked some of the suggestions, much the same for the wider web, I am struggling to find a solution.  In my head it's pretty straight forward, but obviously my skill stops there.

OK.

In one sheet called [Page4] I have an array of particular data, but I want to pull some common data from other pages (Names, Group Numbers, and Locations).  So far I have been successful with drawing this information from one searchable sheet [Page1] using:

Name Manager:

Group = 'Page1'!$A3:$A1000

User = 'Page1'!$B3:$B1000

Location = 'Page1'!$C3:$C1000

>> Then

Page4

Cell A3, { =IF(B3="","",INDEX(Group,MATCH(B3,User,0))) }

Cell B3, Validation > List { =Matrix }  [Matrix = (All users from all pages, remove duplicates)]

Cell C3, { =IF(B3="","",INDEX(Location,MATCH(B3,User,0))) }

So far so good... It is working great.  However, other pages have been added, Page2 and Page3.

Now, I've tried multiple suggestions, they all seem to work just fine - but nothing I can use.

Name Manager:

Group = 'Page1'!$A3:$A1000

Group21 = 'Page2'!$A3:$A1000

Group22 = 'Page3'!$A3:$A1000

User = 'Page1'!$B3:$B1000

User21 = 'Page2'!$B3:$B1000

User23 = 'Page3'!$B3:$B1000

Location = 'Page1'!$C3:$C1000

Location21 = 'Page2'!$C3:$C1000

Location23 = 'Page3'!$C3:$C1000

Each page has a different purpose, so through the Matrix which brings all Users Together, I would like to search all three pages for the relevant user data (Group Number and Location) as laid out in the Page4 Formula

I've tried, as an example:
Cell A3  { =INDEX(A3,((Group)*(Group21)*(Group22)),MATCH(B3,User,0)) }

Result: #SPILL!

Any workable suggestions would be great and appreciated in advance.

===== Additional Information =====

The formula is in A3... and it doesn't need to be coming back as a range.

The formula needs to be drawing information back to the same single cell [Group 03 (A3), belongs to User (B3) and in Location (C3)] when it finds a match to B3 across the three pages; much like A1=Sum('Page1'!A2*'Page2'!A3) would.

The original formula under Page 4 works, I just need to expand on it to test more areas?

Personally, It does appear I may be missing something, such as how to factor in the new search areas of the extra Names. All the data is physically there, the aim is to cut down the repetition through selecting one peace of data (B3) from a drop box (Matrix), then the rest (A3 and C3) is then auto populated.

Hope this helps a little more.

File Link: Planner Muck up 

[#2]

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.

[#3]

Thanks for replying @Patrick2788
No problem.... bear with me while I mock-up a sample workbook for you to work from.

Appreciated.

[#4]

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.

[#5]

@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.

[#6]

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.

[#7]

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?

[#8]

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.

[#9 · Accepted solution]

@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.

[#10]

Apologies for the delay @Patric2788
Much appreciated, I certainly was missing some aspects within the formula! Spot on.

[#11]

You're welcome! I'm glad this solution fits your needs.