vlookup help

  • Thread starter Thread starter Jambruins
  • Start date Start date
J

Jambruins

I have attached a small portion of my file. In worksheet 1 the onl
thing I want to have to change is the date (cell A1). I want cell
B2,B5,B8 to select the team from worksheet 2 in column B tha
corresponds to the date in cell A1. I want cells B3,B6,B9 to selec
the team from worksheet 2 in column C that corresponds to the date i
cell A1. This works fine for the 13-Oct-04 date but if I change it t
14-Oct-04 it doesn't work. Any ideas? Thank you.

Jame

Attachment filename: help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63647
 
Hi
in most cases this is due to formating issues. What exactly does not
work if you change the date. Do you get errors or wrong results?
 
I get the wrong results. For 13-Oct-04 there are 7 instances. It work
perfectly for these 7. this is what is gives me:

13-Oct-04 Team
E Canadiens
E Senators

E Flyers
E Lightning

W Wild
W Blackhawks

W Kings
W Avalanche

W Stars
W Coyotes

W Flames
E Ducks

W Canucks
W Sharks

When I change the date to 14-Oct-04 instead of finding the firs
occurance of 14-Oct-04 and proceeding from there it does this:

14-Oct-04 Team
E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Thrashers
E Hurricanes

E Flyers
E Panthers

W Wild
W Predators

You can see it picks the first occurance multiple times
 
Hi
and now please show the formulas you have used for this. Normall
VLOOKUP will ALWAYS return the first occurence of a match
 
This is the formula I have used

=VLOOKUP($A$1,Schedule!$A$1:$B$100,2,0)

I changed the $A$1 to $A$2 in cell A5 and to $A$3 in cell A8 and so on
 
this works for the 13-Oct-04 date. If I delete the 13-Oct-04 rows i
sheet 2 it resets my formula to $A$1 in all of formulas instead o
keeping $A$2, $A$3, etc. If it would keep the $A$2, $A$3, etc
everything would work out fine
 
Hi
now this explains your problem :-)
As said VLOOKUP always returns the first match but of course if you
change the starting row in your lookup range no wonder VLOOKUP works
for the first entry :-)

You may try the following array formula (entered with CTRL+SHIFT+ENTER)
instead in your first resulting cell: (A3):
=INDEX(Schedule!$B$1:$B$100,SMALL(IF(Schedule!$A$1:$A$100=$A$1,ROW(Sche
dule!$A$1:$A$100)),1))

in your second resulting cell (A5) use:
=INDEX(Schedule!$B$1:$B$100,SMALL(IF(Schedule!$A$1:$A$100=$A$1,ROW(Sche
dule!$A$1:$A$100)),2))

and so on
 
Hi
this formula returns #NUM if there's no match anymore. If you want to
prevent this try
=IF(ISERROR(INDEX(....)),"",INDEX(...))

and replace INDEX(...) with the formula I provided to you
 
acutally it worked great. I forgot to hit ctrl/shift/ent. Thanks fo
all the help Frank
 
Back
Top