AnandMeena
AnandMeena

Reputation: 578

Find position of minimum value in a table range excel

I have a table in excel file, please have a look on below image:-

enter image description here

I want to know the position of cell which has minimum value.

As you can see cell AE26 has the minimum value.

I am trying below formula :-

=CELL("ADDRESS",INDEX(Z7:AJ26,MATCH(MIN(Z7:AJ26),Z7:AJ26,0)))

But it is giving "#N/A" :- "A value is not available to the formula and function."

Not sure what I am doing wrong.

Upvotes: 0

Views: 4748

Answers (1)

teylyn
teylyn

Reputation: 35990

You can't use Match() on a multi-column range.

Your scenario can work if you use a helper column and a helper row to identify which row and which column holds the smallest value of the table.

Consider the following screenshot:

enter image description here

The conditionally formatted table has the defined name MyTable.

Cell D1 calculates the minimum value in the table with the formula

=MIN(MyTable)

D1 has the range name SmallNumber.

Cell I3 has the following formula, copied down

=IF(ISNUMBER(MATCH(SmallNumber,B3:H3,0)),TRUE,FALSE)

Cell B15 has the following formula, copied across:

=IF(ISNUMBER(MATCH(SmallNumber,B3:B14,0)),TRUE,FALSE)

The intersection of the "TRUE" values is the location of the smallest number in the table. If you want to return the cell address of that, you can use the formula in cell D2

=CELL("address",INDEX(MyTable,MATCH(TRUE(),I3:I14,0),MATCH(TRUE(),B15:H15,0)))

You can see a sample file here.

Upvotes: 3

Related Questions