vlookup help

[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

 

[Frank Kabel]

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?

 

[Jambruins]

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

 

[Frank Kabel]

Hi
and now please show the formulas you have used for this. Normall
VLOOKUP will ALWAYS return the first occurence of a match

 

[Jambruins]

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

 

[Jambruins]

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

 

[Jambruins]

acutally it worked great. I forgot to hit ctrl/shift/ent. Thanks fo
all the help Frank