MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Min Match Function needed

• Follow

```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

--
bill gras
```
 0

```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
```
 0

```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
```
 0

```--
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
```
 0

```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
```
 0

```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
```
 0

```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

```
 0

```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
```
 0

```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
```
 0

8 Replies
194 Views

Similiar Articles:

7/15/2012 4:37:25 PM