Finding the lowest value

**ste67@me.com** · 10-23-2013, 02:40 AM

If the user picks a day i need a formula or macro to return back to the user the file with the lowest number in it on the selected Day so if the user picked Monday the formula would look at all numbers in the column Monday and see file 3 has the lowest value but not return the value but the file name Eg "File 3 is the lowest" in cell i3 The day selector must work from an auto filter.. Excel 2003Filter.xls

**benishiryo** · 10-23-2013, 03:13 AM

hi ste67@me.com. because Excel 2003 only allows up to 7 nested levels, you need to separate the formulas into 3 parts. in L1:

Formula:

Please Login or Register  to view this content.

in L2:

Formula:

Please Login or Register  to view this content.

in I2:
=INDEX(D9:D12,L2)

it's such a long process because you are using Autofilter. it seems like Data Validation would be a better choice. i did it in the "new" sheet inside. to do Data Validation List, select D2 & press ALT + D + L. select List & use D3:D5 as the source. in I2 is a much shorter formula:
=INDEX(D9:D12,MATCH(MIN(INDEX(E9:J12,,MATCH(D2,E7:J7,0))),INDEX(E9:J12,,MATCH(D2,E7:J7,0)),0))

**ste67@me.com** · 10-23-2013, 03:30 PM

Hi benishiryo your nearly there yes your way is better now my table is MONDAY A MONDAY B TUESDAY A TUESDAY B WEDNESDAY A and WEDNESDAY B how do i alter the formula to see the A and Bs also thanks Steve