Min Match Function needed - microsoft.public.excel.worksheet.functions

I need a function that will match a name from a range of names wich include multiple names of the same name , to find the lowest number or numbers given to a name or multiple names eg: sheet R! sheet K! A W A K 1 mary 1 mary 2 2 mary 2 2 john 1 3 john 1 3 bill 2 4 john 4 anne "blank" 5 john 7 5 joe 1 6 bill 6 ivon "blank" 7 bill 5 8 bill 2 9 bill 10 anne 11 anne 12 anne 13 joe 3 14 joe 1 15 ivon down to 1200 rows all names and numbers are at random sequinces your help is much appriciated -- bill gras

Try this =MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10)) Where A1 of the active sheet is the name your looking for Mike "bill gras" wrote: > I need a function that will match a name from a range of names > wich include multiple names of the same name , to find the lowest > number or numbers given to a name or multiple names eg: > > sheet R! sheet K! > A W A K > 1 mary 1 mary 2 > 2 mary 2 2 john 1 > 3 john 1 3 bill 2 > 4 john 4 anne "blank" > 5 john 7 5 joe 1 > 6 bill 6 ivon "blank" > 7 bill 5 > 8 bill 2 > 9 bill > 10 anne > 11 anne > 12 anne > 13 joe 3 > 14 joe 1 > 15 ivon > > down to 1200 rows all names and numbers are at random sequinces > > your help is much appriciated > > > > -- > bill gras

Hi Mike Your function does not do what I'm after The result I'm looking for should be according to sheet K! in my example -- bill gras "Mike H" wrote: > Try this > > =MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10)) > > Where A1 of the active sheet is the name your looking for > > Mike > > "bill gras" wrote: > > > I need a function that will match a name from a range of names > > wich include multiple names of the same name , to find the lowest > > number or numbers given to a name or multiple names eg: > > > > sheet R! sheet K! > > A W A K > > 1 mary 1 mary 2 > > 2 mary 2 2 john 1 > > 3 john 1 3 bill 2 > > 4 john 4 anne "blank" > > 5 john 7 5 joe 1 > > 6 bill 6 ivon "blank" > > 7 bill 5 > > 8 bill 2 > > 9 bill > > 10 anne > > 11 anne > > 12 anne > > 13 joe 3 > > 14 joe 1 > > 15 ivon > > > > down to 1200 rows all names and numbers are at random sequinces > > > > your help is much appriciated > > > > > > > > -- > > bill gras

-- bill gras "bill gras" wrote: > Hi Mike > Your function does not do what I'm after > The result I'm looking for should be according > to sheet K! Column K in my example > -- > bill gras > > > "Mike H" wrote: > > > Try this > > > > =MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10)) > > > > Where A1 of the active sheet is the name your looking for > > > > Mike > > > > "bill gras" wrote: > > > > > I need a function that will match a name from a range of names > > > wich include multiple names of the same name , to find the lowest > > > number or numbers given to a name or multiple names eg: > > > > > > sheet R! sheet K! > > > A W A K > > > 1 mary 1 mary 2 > > > 2 mary 2 2 john 1 > > > 3 john 1 3 bill 2 > > > 4 john 4 anne "blank" > > > 5 john 7 5 joe 1 > > > 6 bill 6 ivon "blank" > > > 7 bill 5 > > > 8 bill 2 > > > 9 bill > > > 10 anne > > > 11 anne > > > 12 anne > > > 13 joe 3 > > > 14 joe 1 > > > 15 ivon > > > > > > down to 1200 rows all names and numbers are at random sequinces > > > > > > your help is much appriciated > > > > > > > > > > > > -- > > > bill gras

When seeing this post I believe that it is very much useful if Microsoft could have been introduced Largeif & Smallif Functions like Averageif in 2007. I am sure it is not available in 2007 but I don’t know whether it is there in 2010 or not… -------------------- (Ms-Exl-Learner) -------------------- "bill gras" wrote: > I need a function that will match a name from a range of names > wich include multiple names of the same name , to find the lowest > number or numbers given to a name or multiple names eg: > > sheet R! sheet K! > A W A K > 1 mary 1 mary 2 > 2 mary 2 2 john 1 > 3 john 1 3 bill 2 > 4 john 4 anne "blank" > 5 john 7 5 joe 1 > 6 bill 6 ivon "blank" > 7 bill 5 > 8 bill 2 > 9 bill > 10 anne > 11 anne > 12 anne > 13 joe 3 > 14 joe 1 > 15 ivon > > down to 1200 rows all names and numbers are at random sequinces > > your help is much appriciated > > > > -- > bill gras

Hi, I forgot to mention my formula is an ARRAY formula. See below This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and NOT just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "bill gras" wrote: > > -- > bill gras > > > "bill gras" wrote: > > > Hi Mike > > Your function does not do what I'm after > > The result I'm looking for should be according > > to sheet K! Column K in my example > > -- > > bill gras > > > > > > "Mike H" wrote: > > > > > Try this > > > > > > =MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10)) > > > > > > Where A1 of the active sheet is the name your looking for > > > > > > Mike > > > > > > "bill gras" wrote: > > > > > > > I need a function that will match a name from a range of names > > > > wich include multiple names of the same name , to find the lowest > > > > number or numbers given to a name or multiple names eg: > > > > > > > > sheet R! sheet K! > > > > A W A K > > > > 1 mary 1 mary 2 > > > > 2 mary 2 2 john 1 > > > > 3 john 1 3 bill 2 > > > > 4 john 4 anne "blank" > > > > 5 john 7 5 joe 1 > > > > 6 bill 6 ivon "blank" > > > > 7 bill 5 > > > > 8 bill 2 > > > > 9 bill > > > > 10 anne > > > > 11 anne > > > > 12 anne > > > > 13 joe 3 > > > > 14 joe 1 > > > > 15 ivon > > > > > > > > down to 1200 rows all names and numbers are at random sequinces > > > > > > > > your help is much appriciated > > > > > > > > > > > > > > > > -- > > > > bill gras

On Sun, 27 Dec 2009 02:30:01 -0800, bill gras <billgras@discussions.microsoft.com> wrote: >Hi Mike >Your function does not do what I'm after >The result I'm looking for should be according >to sheet K! in my example Try this formula in Cell K1 of sheet K: =IF(OR(('Sheet R'!A$1:A$15=A1)* ('Sheet R'!W$1:W$15<>"")),MIN(IF(('Sheet R'!A$1:A$15=A1)* ('Sheet R'!W$1:W$15<>""),'Sheet R'!W$1:W$15)),"") Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER Change the 15 in all places to fit the number of data rows you have in Sheet R. Hope this helps / Lars-�ke

Hi Mike H Your formula now works great , but (and most times there is) could you change the formula to show a blank cell insted of a 0.0 Thank You for your time and effort -- bill gras "Mike H" wrote: > Hi, > > I forgot to mention my formula is an ARRAY formula. See below > > This is an array formula which must be entered by pressing CTRL+Shift+Enter > 'and NOT just Enter. If you do it correctly then Excel will put curly brackets > 'around the formula {}. You can't type these yourself. If you edit the formula > 'you must enter it again with CTRL+Shift+Enter. > > > Mike > > "bill gras" wrote: > > > > > -- > > bill gras > > > > > > "bill gras" wrote: > > > > > Hi Mike > > > Your function does not do what I'm after > > > The result I'm looking for should be according > > > to sheet K! Column K in my example > > > -- > > > bill gras > > > > > > > > > "Mike H" wrote: > > > > > > > Try this > > > > > > > > =MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10)) > > > > > > > > Where A1 of the active sheet is the name your looking for > > > > > > > > Mike > > > > > > > > "bill gras" wrote: > > > > > > > > > I need a function that will match a name from a range of names > > > > > wich include multiple names of the same name , to find the lowest > > > > > number or numbers given to a name or multiple names eg: > > > > > > > > > > sheet R! sheet K! > > > > > A W A K > > > > > 1 mary 1 mary 2 > > > > > 2 mary 2 2 john 1 > > > > > 3 john 1 3 bill 2 > > > > > 4 john 4 anne "blank" > > > > > 5 john 7 5 joe 1 > > > > > 6 bill 6 ivon "blank" > > > > > 7 bill 5 > > > > > 8 bill 2 > > > > > 9 bill > > > > > 10 anne > > > > > 11 anne > > > > > 12 anne > > > > > 13 joe 3 > > > > > 14 joe 1 > > > > > 15 ivon > > > > > > > > > > down to 1200 rows all names and numbers are at random sequinces > > > > > > > > > > your help is much appriciated > > > > > > > > > > > > > > > > > > > > -- > > > > > bill gras

Hello Bill, I suggest to use my UDF Pstat with this small change: Case "min", "minimum" If (v(UBound(v))(i, 1) <> "" And vR(UBound(v), obj.Item (s)) > v(UBound(v))(i, 1)) Or vR(UBound(v), obj.Item(s)) = "" Then You can find my UDF here: http://sulprobil.com/html/pstat.html Regards, Bernd

Similiar Articles:

Index, Match within a range of values - microsoft.public.excel ...
I am familiar with INDEX and MATCH functions, but do not ... You do not need the range of values. just a ... The final formula is =INDEX(units,MATCH(MIN ... Index, Match ...

vendor matches - microsoft.public.excel.worksheet.functions ...
... would like to do in Col H is if Col G has a match in ... IF(A1=G1,B1,"") And copy this down as far as you need ... vendor matches - microsoft.public.excel.worksheet.functions ...

EXACT function - but where does it not match? - microsoft.public ...
Try this array formula =MIN(IF(NOT ... formatting, but the MATCH function only seems to let ... range_lookup is FALSE (for an exact match), the table_array does not need ...

min formula - microsoft.public.excel
Try this array formula** : =MIN(IF(A1:A10<>0,A1:A10)) ** array formulas need to be ... Min - microsoft.public.excel.worksheet.functions ... Index Match with Min ...

Explain INDEX MATCH - microsoft.public.excel.worksheet.functions ...
... the Index, Match formula to me? It is used in a spreadsheet and I need ... use a Match function to ... Index Match with Min - microsoft.public.excel.worksheet.functions ...

How can I nest MAX function inside a VLOOKUP? - microsoft.public ...
... and > it > can still be tricky to find the correct match. > > What you need is a array (or CSE) function ... Find Min and Max in variable length arrays - microsoft ...

Match values in more than one column - microsoft.public.excel ...
... formatting, but the MATCH function only ... way of doing this, or do I need some sort of function ... to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH ...

lookup second / last match with two conditions - microsoft.public ...
... table we need to look up value using two conditions: ... Here, the last two are optional. In our example ... the Index function which airline to take and second match ...

Subtraction Function using cells with other functions in them ...
You could try the MIN function. MIN (D11,D13,D15,D17 ... Similar function needed to Excel VLOOKUP - microsoft ... Formula Excel – How to use Excel MATCH Function ...

macro to calculate or make/update formula - microsoft.public.excel ...
the problem is sometimes the min and max ... on different years, etc. i need a macro ... into the address D6 by using the INDEX function, like this: =INDEX(C4:D99,MATCH ...

VLOOKUP | Excel VLOOKUP | Excel Index & Match Functions
How to use the Index and Match functions instead of VLOOKUP in Excel. ... One day, you have a situation where you have the employee name, but need the ...

Using Microsoft Excel Worksheet Functions in Visual Basic [Excel ...
For example, the Concatenate function isn't needed because ... assigned the result of applying the Min function ... For example, you can use the Match worksheet function to ...

MATCH - Excel - Office.com
Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. Syntax. MATCH(lookup_value,lookup_array,match ...

Compare Lists in Excel - MATCH Function - Tech Help ...
You have two or more lists in Excel and you want to find out whether a value in one list exists in another. The MATCH function is what you're in need of.

SQL MIN() Function
The MIN() Function. The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax

7/15/2012 4:37:25 PM