Google Sheets：根据指定列名查找对应列的最后数值

Nice work using MATCH to nail down the column index—let's build on that formula to pull the last numeric value from the column you're targeting. Here are a couple of solid methods depending on your Excel version:

Method 1: Compatible with All Excel Versions #

Combine INDEX and LOOKUP to reliably find the last numeric entry, even if there are blank cells in the column:

=INDEX($H:$AZ, LOOKUP(9^9, INDEX($H:$AZ,,MATCH(B2,$H$1:$AZ$1,0)), ROW($H:$AZ)), MATCH(B2,$H$1:$AZ$1,0))

What this does: #

• INDEX($H:$AZ,,MATCH(B2,$H$1:$AZ$1,0)): Uses your existing MATCH result to isolate the entire target column (the double commas mean we're specifying the column index, not the row).
• LOOKUP(9^9, [target column], ROW($H:$AZ)): 9^9 is a huge number that will land on the last numeric value in the column, returning its row number.
• The outer INDEX then uses that row number and your column index to pull the final value you need.

Method 2: Simplified for Excel 365/2021 (Dynamic Arrays) #

If you have access to XLOOKUP (available in newer Excel versions), this is a cleaner approach that searches from the bottom up:

=XLOOKUP(TRUE, INDEX($H:$AZ,,MATCH(B2,$H$1:$AZ$1,0))<>"", INDEX($H:$AZ,,MATCH(B2,$H$1:$AZ$1,0)), "", 0, 2)

What this does: #

• INDEX($H:$AZ,,MATCH(B2,$H$1:$AZ$1,0)): Again, isolates your target column.
• XLOOKUP(TRUE, [target column]<>"", [target column], "", 0, 2): Looks for the last non-empty cell (which works for numeric values) by starting at the bottom of the column (the final 2 sets the search direction to "bottom to top").

Quick Tips: #

• If you know your data doesn't go beyond row 1000 (for example), replace $H:$AZ with $H$2:$AZ$1000 to speed up the formula (full-column references can be slower).
• Both methods skip blank cells and target the last actual numeric value, regardless of gaps in your data.

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