I have two UDF's that solve two specific problems for me. Both works great.
Now I just wonder if I could acchieve the thing, using only Worksheet
functions as some of the people that would like to use my solution are not
allowed to use macros or install add-ins. And if it can be done, then how?



Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and return
the value found in column 2, exactly as VLOOKUP, only with the option of
deciding which instance I want returned. My UDF does excatly that.
=MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is the number of the
instance, A1:B7 is the data array and 2 is the column containing the value I
like returned. If the lookup-values does not exist or if the requested
instance number is higher than the actual number of instances, #NA! is
returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the reference
cell A1:C5 the array and 3 the column, that shold be returned. But could it
be done with regular worksheet functions.



Jan

Jan,

Try this array formula

=INDEX(B1:B7,(SMALL(IF(INDEX(A1:A7=D1,0),ROW(A1:A7 )),2)))

The 2 at the end is the instance.

HTH

Bob

"Jan Kronsell" wrote in message
...
I have two UDF's that solve two specific problems for me. Both works great.
Now I just wonder if I could acchieve the thing, using only Worksheet
functions as some of the people that would like to use my solution are not
allowed to use macros or install add-ins. And if it can be done, then how?



Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and
return the value found in column 2, exactly as VLOOKUP, only with the
option of deciding which instance I want returned. My UDF does excatly
that. =MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is the number of
the instance, A1:B7 is the data array and 2 is the column containing the
value I like returned. If the lookup-values does not exist or if the
requested instance number is higher than the actual number of instances,
#NA! is returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the
reference cell A1:C5 the array and 3 the column, that shold be returned.
But could it be done with regular worksheet functions.



Jan

On the second one, this array formula

=INDEX(C:C,MATCH(1,(A1:A5<=H1)*(B1:B5=H1),0))

HTH

Bob

"Jan Kronsell" wrote in message
...
I have two UDF's that solve two specific problems for me. Both works great.
Now I just wonder if I could acchieve the thing, using only Worksheet
functions as some of the people that would like to use my solution are not
allowed to use macros or install add-ins. And if it can be done, then how?



Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and
return the value found in column 2, exactly as VLOOKUP, only with the
option of deciding which instance I want returned. My UDF does excatly
that. =MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is the number of
the instance, A1:B7 is the data array and 2 is the column containing the
value I like returned. If the lookup-values does not exist or if the
requested instance number is higher than the actual number of instances,
#NA! is returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the
reference cell A1:C5 the array and 3 the column, that shold be returned.
But could it be done with regular worksheet functions.



Jan

Bob,

Why do you use this in the middle

IF(INDEX(A1:A7=D1,0),ROW(A1:A7))

rather than just

IF(A1:A7=D1,ROW(A1:A7) ?

Thanks,

Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Jan,

Try this array formula

=INDEX(B1:B7,(SMALL(IF(INDEX(A1:A7=D1,0),ROW(A1:A7 )),2)))

The 2 at the end is the instance.

HTH

Bob

"Jan Kronsell" wrote in message
...
I have two UDF's that solve two specific problems for me. Both works
great. Now I just wonder if I could acchieve the thing, using only
Worksheet functions as some of the people that would like to use my
solution are not allowed to use macros or install add-ins. And if it can
be done, then how?



Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and
return the value found in column 2, exactly as VLOOKUP, only with the
option of deciding which instance I want returned. My UDF does excatly
that. =MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is the number of
the instance, A1:B7 is the data array and 2 is the column containing the
value I like returned. If the lookup-values does not exist or if the
requested instance number is higher than the actual number of instances,
#NA! is returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the
reference cell A1:C5 the array and 3 the column, that shold be returned.
But could it be done with regular worksheet functions.



Jan

Thnaks Bob. They both work perfectly.

Jan

Bob Phillips wrote:
On the second one, this array formula

=INDEX(C:C,MATCH(1,(A1:A5<=H1)*(B1:B5=H1),0))

HTH

Bob

"Jan Kronsell" wrote in message
...
I have two UDF's that solve two specific problems for me. Both works
great. Now I just wonder if I could acchieve the thing, using only
Worksheet functions as some of the people that would like to use my
solution are not allowed to use macros or install add-ins. And if it
can be done, then how? Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and
return the value found in column 2, exactly as VLOOKUP, only with the
option of deciding which instance I want returned. My UDF does
excatly that. =MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is
the number of the instance, A1:B7 is the data array and 2 is the
column containing the value I like returned. If the lookup-values
does not exist or if the requested instance number is higher than
the actual number of instances, #NA! is returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the
reference cell A1:C5 the array and 3 the column, that shold be
returned. But could it be done with regular worksheet functions.



Jan

It doesn't work if you don't does it?

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Why do you use this in the middle

IF(INDEX(A1:A7=D1,0),ROW(A1:A7))

rather than just

IF(A1:A7=D1,ROW(A1:A7) ?

Thanks,

Bernie
MS Excel MVP


"Bob Phillips" wrote in message
...
Jan,

Try this array formula

=INDEX(B1:B7,(SMALL(IF(INDEX(A1:A7=D1,0),ROW(A1:A7 )),2)))

The 2 at the end is the instance.

HTH

Bob

"Jan Kronsell" wrote in message
...
I have two UDF's that solve two specific problems for me. Both works
great. Now I just wonder if I could acchieve the thing, using only
Worksheet functions as some of the people that would like to use my
solution are not allowed to use macros or install add-ins. And if it can
be done, then how?



Problem 1.



Data:



A B

John 10

Bill 10

George 14

John 12

Neil 12

George 12

John 20



I need to be able to look up any instance of a name in column 1, and
return the value found in column 2, exactly as VLOOKUP, only with the
option of deciding which instance I want returned. My UDF does excatly
that. =MULVLOOKUP(D1;2;A1:B7;2). D is the reference, 2 is the number of
the instance, A1:B7 is the data array and 2 is the column containing the
value I like returned. If the lookup-values does not exist or if the
requested instance number is higher than the actual number of instances,
#NA! is returned.





Problem 2:



Data:

A B C

1200 1235 Cows

1278 1330 Horses

1400 1600 Pigs

1651 2021 Hens

2100 2213 Sheep



I need to be able to type any number.

If I type a number between 1200 and 1235 it should return Cows.

If I type a number between 1236 and 1277 it should return #NA!

If I type a number between 1400 and 1600 it should return Horses

If I type a number between 1601 and 1650 it should return #NA!



and so on.



Any number less than 1200 or larger than 2213 should return #NA!



Again my UDF works ok: =IntervalLookup(D1;A1:C5;3) where D1 is the
reference cell A1:C5 the array and 3 the column, that shold be returned.
But could it be done with regular worksheet functions.



Jan

Bob,

?

Array entering

=INDEX(B1:B7,(SMALL(IF(A1:A7=D1,ROW(A1:A7)),2)))

instead of

=INDEX(B1:B7,(SMALL(IF(INDEX(A1:A7=D1,0),ROW(A1:A7 )),2)))

works fine for me.

Bernie


"Bob Phillips" wrote in message
...
It doesn't work if you don't does it?

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Why do you use this in the middle

IF(INDEX(A1:A7=D1,0),ROW(A1:A7))

rather than just

IF(A1:A7=D1,ROW(A1:A7) ?
\