Nested function formula: Index+Match, Array+Sumifs

Question

Level 1

7 points

Nested function formula: Index+Match, Array+Sumifs

HELP REQUEST:

1.) I wish to populate several tables which draw their source data from table "1-1 Session Assumptions", using functions Index + match, OR Array, SumIfs. Unfortunately, I've forgotten how. I know that lookup functions have limitations that don't work for my broader purpose.

2.) HELP #1: What is the nested function formula to populate col B ("1 Per Session Revenue") drawing from source data table "1-1 Session Assumptions"?

3.) HELP #2: What is the nested function formula to populate row 4 of 1-1 Sessions Summary table? This draws from both "1-1 Session Assumptions" and "1-1 Sessions Input" table.

I wish to use nested functions Index + match, OR Array, SumIfs since the source data table will be much larger and my database will be tracking daily several people with different per session revenue and expenses. The database will also be used for creating charts of actual vs projected revenue, expense, net revenue, CUMULATIVE #s. So I believe efficient formulas that aren't cell dependent but index or array dependent will be best.

This may or may not help but here's a function I built long ago which used nested functions:

1.) IFERROR(@INDEX('2 - PBC Target Examples Expanded'::$A$1:$BP$65,MATCH($A2,'2 - PBC Target Examples Expanded'::$A,0),MATCH(B$1,'2 - PBC Target Examples Expanded'::$4:$4,0)),0)

Any help would be greatly appreciated to get my brain back in to building efficient functions. -suzi

[Edited by Moderator]

MacBook Pro 16″, macOS 15.3

Posted on Apr 8, 2025 5:20 PM

Reply

Answer 1

Camelot

Level 9

58,299 points

Apr 9, 2025 10:48 AM in response to suekatsue

This would be so much easier if we had a visual to work from.

> 1.) I wish to populate several tables which draw their source data from table "1-1 Session Assumptions", using functions Index + match, OR Array, SumIfs.

Not enough info to go on.

Are you talking direct references?

Or you want to copy a range of cells?

Or you want to extract certain rows/data from the table? Based on what criteria?

There are so many ways of interpreting this ask.

> 2.) HELP #1: What is the nested function formula to populate col B ("1 Per Session Revenue") drawing from source data table "1-1 Session Assumptions"?

Again, define drawing from source'?

Do you want a direct reference to the source table?

Do you want a subset of data from the source table? based on what? limits? ranges? bounds?

> 3.) HELP #2: What is the nested function formula to populate row 4 of 1-1 Sessions Summary table? This draws from both "1-1 Session Assumptions" and "1-1 Sessions Input" table.

Populate it with what? Since I don't know what '1-1 Session Assumptions'; and '1-1 Sessions Input' look like, the best I can do is pull some random data from the tables, but I'm guessing that isn't going to cut it :)

Reply

Answer 2

SGIII

Level 8

37,550 points

Apr 9, 2025 6:18 PM in response to suekatsue

suekatsue wrote:

I know that lookup functions have limitations that don't work for my broader purpose.

That was generally true in the past, with old lookup functions.

But for most purposes now, I think you may find the modern lookup function XLOOKUP easier to use the INDEX MATCH.

XLOOKUP – Apple Support

SG

Reply

Answer 3

suekatsue Author

Level 1

7 points

Apr 29, 2025 2:34 PM in response to Camelot

Thank you for raising those questions and attempting to gain clarity on what I need. I've taken another look at my tables and changed the structure; and, I have turned to XLOOKUP to return the value I want.

Reply