Vlookup Next

[Dom]

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

 

[Jim Thomlinson]

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

 

[Mike H]

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),COUNTIF(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]

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

 

[Mike H]

ooPS,

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

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

Mike

 

[L. Howard Kittle]

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

 

[Dom]

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]

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},COUNTIF(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

 

[Mike H]

You are most welcome

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.