INDEX MATCH across more than one sheet?

[FatalLordes]

Hi all. So I know how to do INDEX MATCH on one sheet, namely:

=INDEX(Main!I:I, MATCH(Report!B3, Main!A:A,0))

But what if I want to do the same thing but across multiple sheets? I tried the obvious, namely:

=INDEX(Main!I:I,Second!I:I, MATCH(Report!B3, Main!A:A,Second!A:A,0))

But that ends up in a "You've entered too many arguments" error. How would I go about this?

Thanks in advance

 

[DanteAmor]

Try:

=LET(a,VSTACK(Main:Second!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Note: The Main - Second sheets must be in that order

Or for example if you have the sheets in the following order: Main, Second and Third, then:

=LET(a,VSTACK(Main:Third!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Referring to the first and the last.

 

[FatalLordes]

Try:

=LET(a,VSTACK(Main:Second!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Note: The Main - Second sheets must be in that order

Or for example if you have the sheets in the following order: Main, Second and Third, then:

=LET(a,VSTACK(Main:Third!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Referring to the first and the last.

 

[FatalLordes]

Try:

=LET(a,VSTACK(Main:Second!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Note: The Main - Second sheets must be in that order

Or for example if you have the sheets in the following order: Main, Second and Third, then:

=LET(a,VSTACK(Main:Third!A1:I1000),FILTER(CHOOSECOLS(a,9),CHOOSECOLS(a,1)=Repor!B3))

Referring to the first and the last.

Hi, Thanks for your help. I tried that (after tweaking it as I've changed sheet names to make it more logical in the future) but I get an error saying formula contains unrecognised text. I have to put the ' ' around the sheets that are numerics otherwise it doesn't recognise them, so is that the issue it is having?

=LET(a,VSTACK('2025-26':'2024-25'!A1:H250),FILTER(CHOOSECOLS(a,8),CHOOSECOLS(a,1)=Admission!B4))

 

[Alex Blakenburg]

The easiest way to check that was to try and construct a VStack formula manually or any aggregation formula such as SUM(), then in edit mode click on the first sheet in the range then holding the shift key down the last sheet in the range. Then select a range and hit enter.
You will find that the it only needs one all inclusive set of ' ',

=LET(a,VSTACK('2025-26:2024-25'!A1:H250),FILTER(CHOOSECOLS(a,8),CHOOSECOLS(a,1)=Admission!B4))

This will apply to sheet names with spaces as well as for the numeric names you are using.

PS: Let me know if you only expect each lookup to return 1 value and you only have a small number of lookup sheets (say up to 3). Then you can use a nested XLookup.

 

[FatalLordes]

The easiest way to check that was to try and construct a VStack formula manually or any aggregation formula such as SUM(), then in edit mode click on the first sheet in the range then holding the shift key down the last sheet in the range. Then select a range and hit enter.
You will find that the it only needs one all inclusive set of ' ',

=LET(a,VSTACK('2025-26:2024-25'!A1:H250),FILTER(CHOOSECOLS(a,8),CHOOSECOLS(a,1)=Admission!B4))

This will apply to sheet names with spaces as well as for the numeric names you are using.

PS: Let me know if you only expect each lookup to return 1 value and you only have a small number of lookup sheets (say up to 3). Then you can use a nested XLookup.

Thanks for showing me that

Now I get a different error, saying "Empty arrays are not supported"? Some cells in the range would be empty, so is that the problem?

Yes, I expect only one value return. Presently there are only two sheets but each year a sheet will be added going forward...

 

[FatalLordes]

The easiest way to check that was to try and construct a VStack formula manually or any aggregation formula such as SUM(), then in edit mode click on the first sheet in the range then holding the shift key down the last sheet in the range. Then select a range and hit enter.
You will find that the it only needs one all inclusive set of ' ',

=LET(a,VSTACK('2025-26:2024-25'!A1:H250),FILTER(CHOOSECOLS(a,8),CHOOSECOLS(a,1)=Admission!B4))

This will apply to sheet names with spaces as well as for the numeric names you are using.

PS: Let me know if you only expect each lookup to return 1 value and you only have a small number of lookup sheets (say up to 3). Then you can use a nested XLookup.

Actually, I'm wrong, there could be one or two value returns...

 

[FatalLordes]

So we are all on the same page, I thought I would describe what I'm trying to do.

Across the sheets that are called 2024-25 and 2025-26 are booking data. As the years progress, new sheets in same format will be added. The data range is always from Row10 to Row 250 but some rows may be blank. A single unique reference for the booking would display in Col H 1-2 times across the sheets. Once for deposit, once for remainder. Sometimes it may only appear once if they pay in full. It is that reference across the sheets I'm looking for as I will then check Col M to see if it was deposit, remainder or total, as well as get other things from the Row like their name, etc...

 

[Alex Blakenburg]

If you want to return multiple values then stick with Dante's filter function.
Blank rows should not impact the formula. If it sometimes will not find a match add ",0" without the quotes at the end of the Filter function ie before the 2nd last bracket.
I can't reproduce the error you are getting. We would probably need a sample of you workbook via a sharing platform to see the error in action.

How are you accommodating the fact that if it returns 2 values it will spill., typically you would wrap it inside a Sum formula ?

 

[FatalLordes]

If you want to return multiple values then stick with Dante's filter function.
Blank rows should not impact the formula. If it sometimes will not find a match add ",0" without the quotes at the end of the Filter function ie before the 2nd last bracket.
I can't reproduce the error you are getting. We would probably need a sample of you workbook via a sharing platform to see the error in action.

How are you accommodating the fact that if it returns 2 values it will spill., typically you would wrap it inside a Sum formula ?

Well, I haven't thought that far yet I only just clicked that potentially it may find two results. To be honest, it could stop looking after the first find I think as I will get all the information I need from that row anyway.... Just have to work out why Dante's filter function isn't working for me.