1. ## VLOOKUP Error (2003)

I am using VLOOKUP and ran into an oddity I cannot figure out. Following is the formula I am using:
=IF(\$C16>0,VLOOKUP(\$C16,'Weekly Data Dump'!\$B\$2:\$C\$1000,2,FALSE),)

C16 contains "985" and successfully finds "985" on the "Weekly Data Dump" worksheet and gives me the appropriate info.

Problem is with row 17. Same basic formula:
=IF(\$C17>0,VLOOKUP(\$C17,'Weekly Data Dump'!\$B\$2:\$C\$1000,2,FALSE),)

C17 contains "1015", and I get #NA. Matter of fact, I get #NA for any number over 1000.

Can anyone tell me why this is happening?

Thank you!
Mary

2. ## Re: VLOOKUP Error (2003)

We'd need to see (a copy of) the workbook.

3. ## Re: VLOOKUP Error (2003)

Thanks for the quick reply, Hans.

Attached is a stripped down version of the file.

I greatly appreciate any insight you may have into the problem.

Mary

4. ## Re: VLOOKUP Error (2003)

Your lookup formula only looks down as far as row 393 so it doesn't look at any numbers over 1000.
Change it to: <code>=IF(\$A3>0,VLOOKUP(\$A3,'Weekly Data Dump'!\$A\$2:\$B\$498,2,FALSE),)</code>
or set up a dynamic range that will expand as you add data.
Regards,
Rory

5. ## Re: VLOOKUP Error (2003)

Great. Thank you.

You mentioned setting up a dynamic range. This sounds like what I need for a long-term solution as my "data dump" sheet will grow weekly.

What would I need to change in my formula to make it dynamic?

Thank you,
Mary

6. ## Re: VLOOKUP Error (2003)

See the attached (I had a feeling you might ask! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) Select Insert->Name->Define from the menu to view the name definition.
HTH

7. ## Re: VLOOKUP Error (2003)

Select Insert | Name | Define...
Enter Data in the 'Names in workbook' box.
Click in the 'Refers to' box and enter the following formula:
<code>
=OFFSET('Weekly Data Dump'!\$A\$2,0,0,COUNTA('Weekly Data Dump'!\$A:\$A)-1,2)
</code>
Click Add, then close the dialog.
Now change the formula in cell B3 on the Assessments sheet to
<code>
=IF(\$A3>0,VLOOKUP(\$A3,Data,2,FALSE),)
</code>
and fill down.

8. ## Re: VLOOKUP Error (2003)

Thank you, thank you, thank you!!!