Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting corresponding test value to a MIN function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting corresponding test value to a MIN function
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. "confused_chris" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting corresponding test value to a MIN function
=index(a2:a5,match(min(b2:b5),b2:b5,0))
confused_chris wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting corresponding test value to a MIN function
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 "Dave Peterson" wrote: =index(a2:a5,match(min(b2:b5),b2:b5,0)) confused_chris wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
struggling with if logical test function | Excel Worksheet Functions | |||
Can someone test this IF function for me, I'm not sure that it works. | Excel Worksheet Functions | |||
IF Function to test formula in a cell | Excel Worksheet Functions | |||
Getting corresponding test value to a MIN function | Excel Worksheet Functions | |||
Function IF / test / Picture result | Excel Worksheet Functions |