Excel中如何获取命名区域内最值的单元格引用或对应行号

Got it, let's break this down for you. You've got a non-contiguous named range mycosts with cells $D$20, $D$31, $D$42, $D$50, and you need to link the min/max values from this range to their corresponding Column A data. Here are practical methods that work across different Excel versions:

Method 1: Compatible with All Excel Versions (INDEX + MATCH) #

This combo is reliable for any Excel version, even older ones that don't support dynamic arrays.

• Get the cell reference of the minimum value:

  =INDEX(mycosts, MATCH(MIN(mycosts), mycosts, 0))
  

  This formula uses MIN(mycosts) to get the smallest value, then MATCH finds its position within the named range, and INDEX pulls the actual cell reference from mycosts.

• Get the row number of the minimum value:
  Wrap the above formula in the ROW function to extract the row number:

  =ROW(INDEX(mycosts, MATCH(MIN(mycosts), mycosts, 0)))
  

• Directly reference Column A for the minimum value's row:
  Skip the row number step and pull the Column A value directly:

  =INDEX(A:A, ROW(INDEX(mycosts, MATCH(MIN(mycosts), mycosts, 0))))
  

Swap MIN with MAX for Maximum Values #

Just replace every instance of MIN with MAX in the formulas above to get the maximum value's cell reference, row number, or corresponding Column A data.

Method 2: Excel 365/2021 (Dynamic Array Functions) #

If you're on a newer Excel version with dynamic array support, you can use more streamlined functions, especially useful if there are duplicate min/max values.

• Get all cell references for minimum values (handles duplicates):

  =FILTER(mycosts, mycosts=MIN(mycosts))
  

  This returns all cells in mycosts that match the minimum value (great if multiple cells have the same min).

• Get all row numbers for minimum values:

  =ROW(FILTER(mycosts, mycosts=MIN(mycosts)))
  

• Directly get all corresponding Column A values:

  =INDEX(A:A, ROW(FILTER(mycosts, mycosts=MIN(mycosts))))
  

Note on Duplicate Values #

If your mycosts range has multiple cells with the same min or max value:

• The INDEX+MATCH method will only return the first occurrence of the value.
• The FILTER method will return all occurrences as a dynamic array (they'll spill into adjacent cells automatically).

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