求助：Google Sheets无AGGREGATE函数，如何替代Excel原公式实现排序匹配

Since Google Sheets doesn’t support Excel’s AGGREGATE function, let’s replicate exactly what your original formula was doing with native Google Sheets tools. Your original formula pulled the Nth matching value from column V (where N is the number of times the current Y value has appeared so far) based on matches in column W. Here are two reliable solutions:

Solution 1: INDEX + SMALL + FILTER (Closest to Original Logic) #

This breaks down the AGGREGATE(15,6,...) logic into Google Sheets-compatible parts:

• FILTER(ROW($2:$14), W$2:$14=Y2) grabs all row numbers where column W matches the current Y value
• SMALL(..., COUNTIF(Y$2:Y2, Y2)) picks the Nth smallest row number (N counts how many times Y2 has shown up up to the current row)
• INDEX(V:V, ...) pulls the corresponding value from column V

Use this formula in cell Z2, then drag it down the column:

=INDEX(V:V, SMALL(FILTER(ROW($2:$14), W$2:$14=Y2), COUNTIF(Y$2:Y2, Y2)))

Solution 2: QUERY Function (More Readable) #

If you prefer a cleaner approach, the QUERY function can filter matching values first, then we pick the Nth result with INDEX:

For text values in Y column: #

=INDEX(QUERY($V$2:$W$14, "SELECT V WHERE W = '"&Y2&"'", 0), COUNTIF(Y$2:Y2, Y2))

For numeric values in Y column: #

Remove the single quotes around Y2 (QUERY handles numbers differently):

=INDEX(QUERY($V$2:$W$14, "SELECT V WHERE W = "&Y2&"", 0), COUNTIF(Y$2:Y2, Y2))

Both solutions will preserve the order from column W and correctly handle duplicate values in Y—just like your original Excel formula did. Test them with your dataset to confirm they work as expected!

内容的提问来源于stack exchange，提问作者Camilo Quevedo