Vlookup Next
 

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

Have you considered applying a filter to the data instead of using a formula?
It will work out a whole pile easier.
--
HTH...

Jim Thomlinson


"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

Hi,

Try this array formula (see below for array formula entry) It looks up a1 in
B1 - B13 and returns column C very similar to VLOOKUP except there is another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

I did think of that, but the calendar is on a seperate sheet from the data,
in a calendar looking format. I don't know how to use a filter on a different
sheet than the one the data is already on. If you know how to that would
help me as well.

Dom

"Jim Thomlinson" wrote:

Have you considered applying a filter to the data instead of using a formula?
It will work out a whole pile easier.
--
HTH...

Jim Thomlinson


"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

ooPS,

II got a typo in that formula, try this one instead

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,A1)+1-E1))

Mike

"Mike H" wrote:

Hi,

Try this array formula (see below for array formula entry) It looks up a1 in
B1 - B13 and returns column C very similar to VLOOKUP except there is another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

Hi Mike,

INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTIF (B1:B13,A1)+1-E1))

I like your formula. A questions if you don't mind. Is the
....*ROW(A1:A13),... to comply with a requirement that both ranges
Column A and B must be the same size? If I try just A1 I get an incorrect
return, namely the first value of column B.

Regards,
Howard

"Mike H" wrote in message
...
Hi,

Try this array formula (see below for array formula entry) It looks up a1
in
B1 - B13 and returns column C very similar to VLOOKUP except there is
another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do
is
list dates and events in a list form and then do a lookup function so it
puts
the event by the proper day. My problem is if there is 2 events on the
same
day, it will always return the first event vlookup finds for that date.
How
do i get past that so vlookup will look for the next event here is the
data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

Mike Thanks that worked, i tried figuring that formula out from one of your
early posts but couldn't get it to work. Now i understand it. Thanks for the
help.

"Mike H" wrote:

ooPS,

II got a typo in that formula, try this one instead

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,A1)+1-E1))

Mike

"Mike H" wrote:

Hi,

Try this array formula (see below for array formula entry) It looks up a1 in
B1 - B13 and returns column C very similar to VLOOKUP except there is another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

Hi,

That bit of the formula produces an array of the rows in which there is a
match so for the data below we get

=INDEX(C1:C13,LARGE({0;0;3;0;0;0;0;0;0;0;0;0;0},CO UNTIF(B1:B13,A1)+1-E1))

i.e the only match is in row 3 so it must be the same size as the other
ranges.

A B C
44 1 11
2 12
44 13
5 14
6 15
7 16
8 17
9 18
10 19
11 20
12 21
13 22
14 23

Mike

"L. Howard Kittle" wrote:

Hi Mike,

INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTIF (B1:B13,A1)+1-E1))

I like your formula. A questions if you don't mind. Is the
....*ROW(A1:A13),... to comply with a requirement that both ranges
Column A and B must be the same size? If I try just A1 I get an incorrect
return, namely the first value of column B.

Regards,
Howard

"Mike H" wrote in message
...
Hi,

Try this array formula (see below for array formula entry) It looks up a1
in
B1 - B13 and returns column C very similar to VLOOKUP except there is
another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do
is
list dates and events in a list form and then do a lookup function so it
puts
the event by the proper day. My problem is if there is 2 events on the
same
day, it will always return the first event vlookup finds for that date.
How
do i get past that so vlookup will look for the next event here is the
data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program

Vlookup Next
 

You are most welcome

"Dom" wrote:

Mike Thanks that worked, i tried figuring that formula out from one of your
early posts but couldn't get it to work. Now i understand it. Thanks for the
help.

"Mike H" wrote:

ooPS,

II got a typo in that formula, try this one instead

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,A1)+1-E1))

Mike

"Mike H" wrote:

Hi,

Try this array formula (see below for array formula entry) It looks up a1 in
B1 - B13 and returns column C very similar to VLOOKUP except there is another
parameter. E1 contains the instance to lookup. So if e1 is a 1 it behaves
exactly like Vlookup but with a 2 in e1 it looks for the second match or
third etc for 3

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,E1)+1-F1))

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

"Dom" wrote:

I am trying to create a calender in excel. What I want to be able to do is
list dates and events in a list form and then do a lookup function so it puts
the event by the proper day. My problem is if there is 2 events on the same
day, it will always return the first event vlookup finds for that date. How
do i get past that so vlookup will look for the next event here is the data
as it appears. Column A represents the calender and the date I want to
lookup. Colum B is the day of the event and c is the event.

A B C
1/2/2010 1/1/2010 New Years Day Bowl games
1/3/2010 1/25/2010 Moms Birthday
1/4/2010 1/10/2010 Dads Birthday
1/5/2010 1/24/2010 Students Return
1/6/2010 1/24/2010 Fun time Project
1/30/2010 Class Begins
1/1/2010 Work on Excel Program