Index function

  • Thread starter Thread starter Robert Doick
  • Start date Start date
R

Robert Doick

I've managed to loose a helpful formula that I used to use
when I moved jobs (I got some help on here previously to
come up with it).

So - help appreciated again. I want to find a minimum
value across some columns, and then once found, look up
the column to it's title. I think I was using the MIN
function, then running it with a MATCH, to feed the column
into the INDEX function, with the array running up to row
1 to pick up the title value.

Any help, or advice on a better way would be appreciated.
 
Robert,

Try the following formula

=INDEX(A1:F1,1,MATCH(MIN(A2:F2),A2:F2,0))

This will return the value from A1:F1 corresponding to the minimum value
found in A2:F2.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Robert Doick said:
I've managed to loose a helpful formula that I used to use
when I moved jobs (I got some help on here previously to
come up with it).

So - help appreciated again. I want to find a minimum
value across some columns, and then once found, look up
the column to it's title. I think I was using the MIN
function, then running it with a MATCH, to feed the column
into the INDEX function, with the array running up to row
1 to pick up the title value.

Any help, or advice on a better way would be appreciated.
Click to expand...

Maybe this will help:
=INDEX(A1:K1,MATCH(MIN(A2:K2),A2:K2,0))
 
Assume that your table is called MyTable

=INDEX(MyTable,MAX(IF(MyTable=MIN(MyTable),ROW(MyTable)))+1-CELL("row",MyTab
le),MAX(IF(MyTable=MIN(MyTable),COLUMN(MyTable)))+1-CELL("col",MyTable))

entered with ctrl + shift & enter
 
The others have probably given you the answer you need, but just so as you know,
you can always search the newsgroups for previous postings using Google. You
are able to put your name as the author in, so that you can find the thread, and
then just trawl through the thread till you find the answer you used before .
The advanced search page is here:-

http://groups.google.com/advanced_group_search?hl=en
 
Back
Top