Index Match formula with 2 criteria for numbers

Well, a bit of guessing here from the example you gave but it looks as if you want to retrieve the score for a given AllyCode and MapStatId. If that assumption is correct then you can use SUMIFS like this:

The formula in C2 filled/copied right and down:

=SUMIFS(stats::$G,stats::$D,$B2,stats::$C,C$1)

SUMIFS takes arguments like this:

This works if there is only one score for each pair of AllyCode and MapStatId. AVERAGEIFS would also work.

Note that your original INDEX MATCH combination doesn't work, either in Excel or Numbers, because for a two-dimensional range. one MATCH needs to give you a vertical coordinate and one a horizontal coordinate.

If needed to retrieve a non-numerical value unlike score then you could add an extra column concatenating AllyCode and MapStatid and match against that column. Then you need to match horizontally to get the other argument for INDEX. That's more complicated than just using SUMIFS, but post if you need that.

SG

I deleted my last post to determine why I was still getting results (incorrect ones) when there was no match. Figured it out.

I have been thinking about this multiple-criteria search problem. With MINFS and MAXIFS we have new options for multiple-criteria searches. It is no longer necessary to concatenate two or more fields and search on that. All it takes is one new column with the ROW function in it and you can search on any combination of the other columns. Use MINIFS to search for the multiple criteria and have it return the row number from that new column. Then use INDEX to get the data from whatever column you want it from. MINIFS does a top-down search. MAXIFS does a bottoms-up search.

MINIFS will return 0 (not an error) if there is no match so we have to check for that.

IF(MINIFS(Data Table::D,Data Table::A,A,Data Table::B,B)=0,"not found",INDEX(Data Table::C,MINIFS(Data Table::D,Data Table::A,A,Data Table::B,B)))

It would less complicated looking if I did the MINFS in one column and referenced it twice in the formula vs calling it twice in the formula.

When posting screenshots in this forum it's usually less confusing if you first turn off 'Use header names as labels' and Numbers > Preferences > General.

What value do you want to appear in C2?

SG

Good one for looking up non-numerical values! So SUMIFS for numerical, an extra column and INDEX MAXIFS for non-numerical.

SG

SGIII wrote:

Good one for looking up non-numerical values! So SUMIFS for numerical, an extra column and INDEX MAXIFS for non-numerical.

SG

It can also be used instead of SUMIFS if there may be multiple matches. It gives a singular match where SUMIFS might sum multiple matches. If there is no possibility of multiple matches, though, SUMIFS is much simpler.

deleted. need to test some more