Multiple VLOOKUP?

  • Thread starter Thread starter Rigel
  • Start date Start date
R

Rigel

I have a workbook that contains multiple worksheets that
are my database.The database is updated every weekday
(using VBA code)when the user presses a button. Each entry
in the database is stamped with the date the button is
pressed(TODAY).The data is collected on a "master"
worksheet,each column is a weekday with the data below.See
below.....

(wed) (thur) (fri)
B C D E
1 1/21/2004 1/22/2004 1/23/2004
2 xa DATA DATA =VLOOKUP($E$1,INDIRECT(EV6),5,FALSE)
3 yb DATA DATA
4 zc DATA DATA

(cells marked DATA are also VLOOKUP formulas)
The problem I'm having is with the Friday column.There is
a possibility that the database will not be updated on
friday, it might not be updated till Sat. or Sun. Since
the database is stamped with the date it is updated my
VLOOKUP formula will not work if this occures on SAT or
SUN.I know this is wrong but in the example above the
formula would look like this...
VLOOKUP(1/23/2004,1/24/2004,1/25/2004,INDIRECT
(EV6),5,FALSE)
(I think I might need to use MATCH but not sure)
I hope I have made myself clear.
Thanks in advance,

Rigel
 
Here is a formula you can modify to suit. It assumes that H5 is a date
serial.
=H5+CHOOSE(WEEKDAY(H5),1,1,1,1,1,1,2)
12/05/03 Fri 6
12/06/03 Sat 7
12/08/03 Mon 2
12/09/03 Tue 3
12/10/03 Wed 4
12/11/03 Thu 5
12/12/03 Fri 6
12/13/03 Sat 7
12/15/03 Mon 2
12/16/03 Tue 3
 
Don,
Maybe I should have posted the question in New User
group,because right now that how I feel.<g>
I have worked with with the formula you supplied for three
day now and still do not understand how it works or how to
use it.Using Google I have read alot of posts with the
CHOOSE/WEEKDAY combo trying to understand,no luck.I can
get the next day with your formula but not two days,which
is what I need.Do I replace $E$I with $H$5 in the formula?
If it not to much trouble would you please explain what I
would need to do to get it to work in the original formula
I posted.=VLOOKUP($E$1,INDIRECT(EV6),5,FALSE)
Also maybe a brief explaination of how CHOOSE/WEEKDAY works
because from my reading it seems like something I need to
learn.
Thanks Again,
Rigel
(overworked in need of vacation)
 
What you need to do is look at each formula separately and learn to
understand it by itself. Then, put together. Look in the HELP index for
CHOOSE and then look for WEEKDAY. Then you can understand how they can work
together. Choose just works with consecutive numbers starting with 1 and
weekday simply gives you the number of the day of the week.
 
Back
Top