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

Table 2

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!

CodePudding user response：

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) & "'")

CodePudding user response：

Yes, it's an option to iterate VLOOKUP in a column:

=ARRAYFORMULA(IF('Table 2'!A:A="","",VLOOKUP('Table 2'!A:A,'Table 1'!A:B,2,0)))

(You may start from A2:A if you have headers)

Or you can do it in the cell where you're calculating itself wrapping this previous formula. Try it based on the amount of operations you may need to do and the data. For example:

=MAX(ARRAYFORMULA(IF('Table 2'!A:A="","",VLOOKUP('Table 2'!A:A,'Table 1'!A:B,2,0))))

CodePudding user response：

try:

=LAMBDA(x, {MAX(x), MIN(x), AVERAGE(x)})
 (INDEX(IFNA(VLOOKUP(D1:D3, A1:B6, 2, ))))