Google Sheets Formula combining ARRAYFORMULA, VLOOKUP, and MAX functions

Question

I have a table (eg.- "TableA") with categories and values, like so:

Category	Value
A	3
A	1
A	4
B	1
B	5
B	9
C	6
C	5
C	3
C	5

I have another table with a list of unique categories, like so:

Category	Max Value
A
B
C

The number of categories will change periodically, so I would like an ARRAYFORMULA to find the max value for each category. There are thousands of categories, in fact, so I would like to avoid using a QUERY inside of an ARRAYFORMULA for performance's sake. Any takers?

Answer 1

Put this in the header cell with Max Value:

={"Max Value"; ARRAYFORMULA(IF(A2:A = "",, VLOOKUP(A2:A, QUERY({'Data Sheet'!A:B}, "SELECT Col1, MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1", 1), 2,)))}

Assumptions (fix the formula accordingly to your actual sheets):

• categories are statically set and are in column A:A
• data from the first table is on sheet Data Sheet in columns A:B.