Min Match Function needed

  • Thread starter Thread starter bill gras
  • Start date Start date
B

bill gras

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
 
Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike
 
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


bill gras said:
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
Click to expand...
 
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…
 
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
 
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
Click to expand...

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
 
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
 
Back
Top