Finding Smallest Value

I am doing a cost comparison of four numbers. I need to find the smallest
value, so I use the MINA function. Is it possible to take this solution and
tell me what row it is from, or return the corresponding category?
Ex.

A1- United A2- $1
B1- Mart B2- $2
C1- Horizon C2- $3
D1- MS Walker D2- $4

So, after the smallest value is found, is there a way to produce as the
solution the corresponding purveyor? So, if A2 is lowest value can I have
the solution refer back to A1?

Try

=INDEX(A1:D1,MATCH(MAX(A2:D2),A2:D2,0))

One way:

=INDEX(A:A,MATCH(MIN(B:B),B:B,FALSE))

In article <BA3E9CA7-1AE7-484F-AD28-7E217F8CA354@microsoft.com>,
Phil <Phil@discussions.microsoft.com> wrote:

> I am doing a cost comparison of four numbers. I need to find the smallest
> value, so I use the MINA function. Is it possible to take this solution and
> tell me what row it is from, or return the corresponding category?
> Ex.
>
> A1- United A2- $1
> B1- Mart B2- $2
> C1- Horizon C2- $3
> D1- MS Walker D2- $4
>
> So, after the smallest value is found, is there a way to produce as the
> solution the corresponding purveyor? So, if A2 is lowest value can I have
> the solution refer back to A1?

Try something like this, Phil:

With your table of information in A1:D2

This formula finds the lowest value in A2:D2 and returns the corresponding
purveyor in A1:D1.
F1: =INDEX(A1:D1,1,MATCH(MINA(A2:D2),A2:D2,0))

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Phil" wrote:

> I am doing a cost comparison of four numbers. I need to find the smallest
> value, so I use the MINA function. Is it possible to take this solution and
> tell me what row it is from, or return the corresponding category?
> Ex.
>
> A1- United A2- $1
> B1- Mart B2- $2
> C1- Horizon C2- $3
> D1- MS Walker D2- $4
>
> So, after the smallest value is found, is there a way to produce as the
> solution the corresponding purveyor? So, if A2 is lowest value can I have
> the solution refer back to A1?

Thanks... this was perfect. One other question, how can I copy and paste
this all the way down my spreadsheet without having to go thru and manually
adjust all the formulas, so it references the same cells?...A1:D1

"Ron Coderre" wrote:

> Try something like this, Phil:
>
> With your table of information in A1:D2
>
> This formula finds the lowest value in A2:D2 and returns the corresponding
> purveyor in A1:D1.
> F1: =INDEX(A1:D1,1,MATCH(MINA(A2:D2),A2:D2,0))
>
> Is that something you can work with?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Phil" wrote:
>
> > I am doing a cost comparison of four numbers. I need to find the smallest
> > value, so I use the MINA function. Is it possible to take this solution and
> > tell me what row it is from, or return the corresponding category?
> > Ex.
> >
> > A1- United A2- $1
> > B1- Mart B2- $2
> > C1- Horizon C2- $3
> > D1- MS Walker D2- $4
> >
> > So, after the smallest value is found, is there a way to produce as the
> > solution the corresponding purveyor? So, if A2 is lowest value can I have
> > the solution refer back to A1?

Try something like this:

F1: =INDEX($A$1:$D$1,1,MATCH(MINA(A2:D2),A2:D2,0))

Note: Dollar Signs ($) in a reference "lock in" that part of the reference.
For more on how they work....

See Excel Help:
About cell and range references
->The difference between relative and absolute references.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Phil" wrote:

> Thanks... this was perfect. One other question, how can I copy and paste
> this all the way down my spreadsheet without having to go thru and manually
> adjust all the formulas, so it references the same cells?...A1:D1
>
> "Ron Coderre" wrote:
>
> > Try something like this, Phil:
> >
> > With your table of information in A1:D2
> >
> > This formula finds the lowest value in A2:D2 and returns the corresponding
> > purveyor in A1:D1.
> > F1: =INDEX(A1:D1,1,MATCH(MINA(A2:D2),A2:D2,0))
> >
> > Is that something you can work with?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Phil" wrote:
> >
> > > I am doing a cost comparison of four numbers. I need to find the smallest
> > > value, so I use the MINA function. Is it possible to take this solution and
> > > tell me what row it is from, or return the corresponding category?
> > > Ex.
> > >
> > > A1- United A2- $1
> > > B1- Mart B2- $2
> > > C1- Horizon C2- $3
> > > D1- MS Walker D2- $4
> > >
> > > So, after the smallest value is found, is there a way to produce as the
> > > solution the corresponding purveyor? So, if A2 is lowest value can I have
> > > the solution refer back to A1?