Fill down error for Vlookup

[Guest]

Hi,

I have created a VLOOKUP formula which creates the correct answer for me.

However, i need to fill down the formula for the whole spreadsheet (about
38,000 rows) that i am working on. When i do this it moves the lookup table
down one row each time i fill.

Is there some way that i can copy or fill down the formula without having
the lookup table in the formula change with every row?

Thanks

 

[Dave Peterson]

You can either give that lookup table a nice name.

Select the table
Insert|Name|Define

Then your =vlookup() will look more like:

=vlookup(a1,myTable,2,false)

Or you could refer to your table using absolute addresses:

=vlookup(a1,sheet2!$a$1:$c$999,2,false)
(Notice the extra $'s)

Or you could just use the whole column (if there's nothing on the worksheet
below that table):

=vlookup(a1,sheet2!a:c,2,false)

 

[Guest]

Thanks for your help dave. The suggestion worked.

Much appreciated.