Getting corresponding test value to a MIN function

[Guest]

I have a sheet that looks something like this...

Supplier Price Low Price Supplier Low Price
Jimmy's Widgets 10 7
Widget World 45
Widget Discount 7
Widget's R Us 35565

I am able to get "7" to come up as my low price with this function...
=MIN(B2:B5) but I want the name of the corresponding supplier to appear under
"Low Price Supplier". I have tried various combinations of IF, MATCH, etc.
with no success.

Help Please!!

Thanks,

Chris the excel peon

 

[Guest]

Here is one way to do it (maybe not the best, but it worked for my test
Create a function like so:
Function myLookup(myInt As Integer, myRng As Range) As String
For Each cell In myRng
If myInt = cell.Value Then
myLookup = cell.Offset(, -1).Value
Exit For
End If
Next cell
End Function
And then in the worksheet, enter a formula like so:
=mylookup(D1,B1:B4) where d1 is the low price value you are looking up and
b1:b4 is where the Price is. Change types as necessary (from the data given,
it looked like Integer would work for the test.) Or if you were looking for
max, you could change it to long.

 

[Dave Peterson]

=index(a2:a5,match(min(b2:b5),b2:b5,0))

I have a sheet that looks something like this...

Supplier Price Low Price Supplier Low Price
Jimmy's Widgets 10 7
Widget World 45
Widget Discount 7
Widget's R Us 35565

I am able to get "7" to come up as my low price with this function...
=MIN(B2:B5) but I want the name of the corresponding supplier to appear under
"Low Price Supplier". I have tried various combinations of IF, MATCH, etc.
with no success.

Help Please!!

Thanks,

Chris the excel peon

 

[Guest]

Hey Dave,

Thanks - you're the man! I was a little confused by the first reply but
yours works out very nicely!

Cheers,

"no longer" Confused Chirs