Formula to look up a range of values and return the max value?

0
votes

I'm trying to create a formula that takes a list of values, correlates them to respective values in a table, and determines the maximum of the respective values. I've tried an array function (shown below) that is a combination of "max" and "vlookup", but the problem is that it just returns the first value. For example, in my screenshot below (apologies, can't paste photos directly yet) I want to take the range of IDs 1004 to 1008, grab the values that they correspond to (located in column B), and then determine the maximum value. My formula outputs "8", but the max value should clearly be 20, at ID 1008.

link to excel example

How can I accomplish this in one formula, if possible? I'm working on an older version of Excel, and I believe newer ones have a function called "Maxifs". I came across equivalent versions of "maxifs", but all of them take one lookup, listed multiple times in a table, but I'm looking for multiple lookups, listed once in a table. Thank you.

2
excelexcel-formulavlookupmaxifs

2 Answers

0
votes

Use this array formula:

=MAX(INDEX(B2:B11,N(IF({1},MATCH(D2:D6,A2:A11,0)))))

Depending on one's version one may need to confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

If the ID are unique we can use:

=MAX(SUMIFS(B:B,A:A,D2:D6))

The same caveat of using Ctrl-Shift-Enter applies

Version in newer Excels with dynamic array formula:

MAXIFS:

=MAX(FILTER(B2:B11,ISNUMBER(MATCH(A2:A11,D2:D6,0))
0
votes

If you have the AGGREGATE function, you can use this non-array formula:

=AGGREGATE(14,6,1/(TRANSPOSE(Range)=ID)*Value,1)

where Range, ID, and Value represent the obvious range references.