Is there a way to look up values in one Table using a list of primary keys? Google Sheets

Question

I'm working on a Google Sheets spreadsheet that uses MAXIFS, MINIFS, etc. I have one table that contains primary keys and non-unique numerical values associated with each key. I also have a filtered list of primary keys that I want to search. The following examples are simplified versions of what I have:

Table 1

People	Value
Alice	413
Bob	612
Carol	612
Dylan	1111
Eve	413
Frank	612

Table 2

People to Lookup
Alice
Carol
Eve

My goal is to look through Table 1 for the primary keys specified in Table 2, get the corresponding values from Table 1 in Column B, and then perform an operation on those values. For example, I need to use MAX, so it would yield "612" as the result, since that is the largest value in the specified list. I also want to use MIN, AVG, and MODE, if possible.

What formulas do I need to use to achieve this result? Do I need to make proxy tables or have some other helper tool?

I've tried looking up ways to use MAXIFS, VLOOKUP, and MATCH, but I'm either using the wrong formulas or putting in the wrong ranges. I've tried =MAXIFS('Table 1'!$B$2:$B, 'Table 1'!$A$2:$A, 'Table 2'!$A$2:$A), but this results in an error. Maybe I could iterate VLOOKUP for all of the items in Table 2? Or maybe MATCH would be a better fit? Any help is greatly appreciated. Thanks!

Answer 1

Try-

=MAX(FILTER(B2:B7,INDEX(COUNTIFS(D2:D4,A2:A7))))

Then use other function like MIN, AVG etc.

Query() should also work.

=QUERY(A2:B,"select MAX(B) where A matches '" & TEXTJOIN("|",TRUE,D2:D) & "'")