Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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

Can these look ups be done using Worksheets functions
 

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) ?
\