Troubles with VLOOKUP (keep getting #REF)

  • Thread starter Thread starter shadestreet
  • Start date Start date
S

shadestreet

Trying to use the VLOOKUP function, but I keep getting #REF errors
despite the fact that I am using the function wizard.

Here is the formula I am using, and I will describe what is in eac
cell/column:

=VLOOKUP(D5,C9:C39,13,FALSE)

D5 = I am using a function to find todays date, specifically "=TODAY()
Current value is 3/12/04

C9:C39 = Range of dates, I input the first day of the month in C
(3/1/04) and then dragged down the rest of the dates.

13 = This is column M, and this is probably what I am messing up on.
In this column I have the number of workdays remaining in a year, th
value I want to retrieve is in the row that matches todays date fro
column C. Note that there is a formula in this cell to calculat
remaining workdays, but I dont think posting it is relevan.t

FALSE = Shouldnt matter, the data is sorted in ascending order an
there is an exact value to match.

Ok, any suggestions? What am I doing wrong
 
Hi
try
=VLOOKUP(D5,C9:M39,11,FALSE)
You have to specify in the second parameter the whole range (including
the return column). Also the first column of this lookup range (in your
case col. C) hast the column index '1' (and not '3' in your example) ->
column M: column index = 11


though this will work you may consider a different approach just using
a simple formula to calculate the remaining working days for the year:
=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY()),12,31))
 
Thanks Frank, I had just figured this out by trial and error on my ow
before you posted, but it is nice to see how helpful this board i
(first time posting).

thanks for the additional formula too
 
Hi Shadestreet!

Your VLOOKUP table should be absolutely referenced before you copy
down
Your VLOOKUP table only has one column but you are looking at column
13

But from what you say, I think there might be better approaches. What
do you want the formula to return. Give a few examples.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
shade

VLOOKUP range has to include the Lookup value, table_array,
column_index_number(which is part of the table_array)

Move your lookup value from D5 to B5 then include your column M in the table
array.

=VLOOKUP(B5,C3:M39,11,FALSE)

Note: the 11 and not 13 because your table starts at C and is only 11 columns
wide.

Gord Dibben Excel MVP
 
Back
Top