大表格无法使用VLOOKUP，求用MAX、INDEX、MATCH实现每月最大值的方法

Hey Barry, let's work through this issue together. That oddly formatted large table can definitely throw a wrench in standard functions like VLOOKUP, but we can get the monthly max values you need with a few targeted function tweaks—let's break it down step by step:

1. 提取当前工作表中每个月份的最大值 #

First, let's tackle pulling the max value for each month directly from your tricky table. The key here is targeting rows/columns tied to the same month, even if the table layout is non-standard.

如果你用Excel 365/2021/2019（支持MAXIFS） #

MAXIFS is perfect for this scenario—it lets you filter your data by a month criteria and grab the max in one clean formula. Let's assume:

• Your month labels (e.g., "Jan", "Feb" or date values like 1/1/2024) are in column A
• The numeric values you want to find the max of are in column C

For a hardcoded month like "Jan":

=MAXIFS($C:$C, $A:$A, "Jan")

If you're referencing a cell with the month name (say cell E2 has "Jan"):

=MAXIFS($C:$C, $A:$A, E2)

If your months are stored as dates, use DATE and EOMONTH to target the full month range:

=MAXIFS($C:$C, $A:$A, ">="&DATE(2024,1,1), $A:$A, "<="&EOMONTH(DATE(2024,1,1),0))

如果你用旧版Excel（不支持MAXIFS） #

We'll use an array formula with MAX and IF—the issue you ran into with MAX+INDEX+MATCH was probably missing the array entry step. Using the same column setup as above:

=MAX(IF($A:$A="Jan", $C:$C, ""))

Critical note: For pre-365 Excel, you need to enter this by pressing Ctrl+Shift+Enter (instead of just Enter) to trigger the array calculation.

2. 跨工作表关联月份并匹配最大值 #

To pull that monthly max into another worksheet (let's call your original table sheet Data and the target sheet Summary), just adjust the formula to reference the other sheet.

用MAXIFS跨表 #

In your Summary sheet, if cell A2 has the month you want to match, use:

=MAXIFS(Data!$C:$C, Data!$A:$A, Summary!A2)

用数组公式跨表（旧版Excel） #

Again, use Ctrl+Shift+Enter to activate the array calculation:

=MAX(IF(Data!$A:$A=Summary!A2, Data!$C:$C, ""))

Why your original MAX+INDEX+MATCH attempt failed #

Chances are it was one of these common pitfalls:

• Your table has merged cells or non-contiguous data regions, which breaks the range references in INDEX/MATCH
• You didn't use Ctrl+Shift+Enter to trigger the array calculation (required for pre-365 Excel)
• The month criteria didn't exactly match (e.g., extra spaces, mismatched date formats—use TRIM() on text months or MONTH() on dates to standardize)

If your table is extremely non-standard (months mixed in rows and columns), use FILTER (365 only) to first gather all values for a month, then take the max:

=MAX(FILTER(Data!$A:$Z, (Data!$A:$A="Jan")+(Data!$1:$1="Jan")))

Adjust the ranges to match your specific table layout.

内容的提问来源于stack exchange，提问作者Barry van der Ark