Is there a way to find the cell reference from a table using the .

[Guest]

I am looking for a way to locate the cell that has the min value in a table.
I can obviously return the min value and then do a find, but I was hoping for
a more elegant solution - for example in a table of daily information being
able to find the min value and on which date (column header) it occurs.

 

[Jason Morin]

Min. value cell address:

=ADDRESS(MIN(IF(MIN(tbl)=tbl,ROW(tbl))),MIN(IF(MIN(tbl)
=tbl,COLUMN(tbl))),4)

Array-entered, meaning press ctrl/shift/enter. "tbl" is
the range for your table.

Column header of min. value:

=INDIRECT(ADDRESS(ROW(tbl),MIN(IF(MIN(tbl)=tbl,COLUMN
(tbl)))))

Array-entered as well.

HTH
Jason
Atlanta, GA

 

[Guest]

Hi JP,
Try using the following formula entered as an array (Shift-Ctl_Enter)
where data = your data range
=ADDRESS(MIN(IF(data=MIN(data),ROW(data),"")),COLUMN(data))

 

[Domenic]

Assuming that your table is contained in A2:E6 (Column A contains text
values with some description), and that your dates are contained in
B1:E1, try the following...

=INDEX(A1:E1,MIN(IF(B2:E6=MIN(B2:E6),COLUMN(B2:E6))))

....entered using CONTROL+SHIFT+ENTER.

Note that if there are more than one value that equals the minimum, the
formula will only return the date it first occurred.

To return the date it last occurred, try the following...

=INDEX(A1:E1,MAX(IF(B2:E6=MIN(B2:E6),COLUMN(B2:E6))))

....entered using CONTROL+SHIFT+ENTER.

Hope this helps!