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
|