Formula To Bring The Best Name 2
 

I posted a question yesterday & got a good reply from Jacob & now I can't
find it.

The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4), there
costs for a item in (C19:L19) I want to bring the cheapest supplier name into
cell M4.

Jacob replied:-

=INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0))

I now need to exclude the $0.0000 values in C19:L19

Formula To Bring The Best Name 2
 

I now need to exclude the $0.0000 values in C19:L19

Try this...

=INDEX(C4:L4,MATCH(SMALL(C19:L19,COUNTIF(C19:L19,0 )+1),C19:L19,0))

--
Biff
Microsoft Excel MVP


"peterh" wrote in message
...
I posted a question yesterday & got a good reply from Jacob & now I can't
find it.

The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4),
there
costs for a item in (C19:L19) I want to bring the cheapest supplier name
into
cell M4.

Jacob replied:-

=INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0))

I now need to exclude the $0.0000 values in C19:L19

Formula To Bring The Best Name 2
 

Use the following array formula:

=INDEX(C4:L4,
MATCH(MIN(IF(C19:L19<0,MAX(C19:L19)+1,C19:L19)),C1 9:L19,0))

This assumes that there is at least one value in C19:L19 that is
greater than zero.

This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.



On Wed, 20 Jan 2010 13:06:01 -0800, peterh
wrote:

I posted a question yesterday & got a good reply from Jacob & now I can't
find it.

The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4), there
costs for a item in (C19:L19) I want to bring the cheapest supplier name into
cell M4.

Jacob replied:-

=INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0))

I now need to exclude the $0.0000 values in C19:L19