MAX returns correct column, but row -1?
 

I want to look up the closest completed Week Ending Date & return the
value of remaining Vacation Time from column I, for the appropriate
week ending date.

'WEEKDAY' = Week Ending Dates for 2007
I12:I63 = Vacation Time Remaining

I can't figure out why I continue to get an #N/A error with this
formula:
{=VLOOKUP(MAX(WEEKDAY<=TODAY()),I12:I63,1,FALSE)}


Or why it returns the appropriate column, minus 1 row, when a value is
entered into column H (Vacation Used for Week).
{=MAX(IF(WEEKDAY<=TODAY(),I12:I63))}

Example:
{=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} returns 20.17 hours remaining if
I didn't use any vacation time that week (column H).
However, I used '10 hours' of vacation that week (column H), the
formula returns the value in the cell above 20.17.

Any thoughts? Either formula would work, I believe, if I could figure
out

Thanks,
Ryan

MAX returns correct column, but row -1?
 

You are using a cannon to kill a fly. Even worse, you missed. <g

Try something simple like:

=INDEX(I12:I63,MATCH(TODAY(),WEEKDAY,1))
__________________________________________________ ______________________


"Ryan" wrote in message
ups.com...
I want to look up the closest completed Week Ending Date & return the
value of remaining Vacation Time from column I, for the appropriate
week ending date.

'WEEKDAY' = Week Ending Dates for 2007
I12:I63 = Vacation Time Remaining

I can't figure out why I continue to get an #N/A error with this
formula:
{=VLOOKUP(MAX(WEEKDAY<=TODAY()),I12:I63,1,FALSE)}


Or why it returns the appropriate column, minus 1 row, when a value is
entered into column H (Vacation Used for Week).
{=MAX(IF(WEEKDAY<=TODAY(),I12:I63))}

Example:
{=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} returns 20.17 hours remaining if
I didn't use any vacation time that week (column H).
However, I used '10 hours' of vacation that week (column H), the
formula returns the value in the cell above 20.17.

Any thoughts? Either formula would work, I believe, if I could figure
out

Thanks,
Ryan

MAX returns correct column, but row -1?
 

All it takes sometimes is a new set of eyes.

Thanks for your help, this works awesome! :)

MAX returns correct column, but row -1?
 

You're most welcome!
__________________________________________________ _______________________

"Ryan" wrote in message
ups.com...
All it takes sometimes is a new set of eyes.

Thanks for your help, this works awesome! :)