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.
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.
If you have the
AGGREGATE
function, you can use this non-array formula:where
Range
,ID
, andValue
represent the obvious range references.