How fill vlookup formula

[Brett1966]

I've set up a spreadsheet with the data on one sheet, and on another sheet the VLOOKUP formula.
This is the formula =IF(ISBLANK(A70),"",VLOOKUP(A70,'2015Quotes'!A2:J1000,2,FALSE))
When I try to fill it across 8 cells, all I want to change is the last number, ie 2 then 3 then 4 etc. But it changes A70 to B70 to C70 and so on. It also changes A2:J1000 to B2:K1000 etc.
How do I make change the last number only when I drag it across?

[cbatrody]

Hi,

Welcome to the forum.

Try the following:

=IF(ISBLANK($A70),"",VLOOKUP($A70,'2015Quotes'!$A$2:$J$1000,COLUMN(B$1),FALSE))

[samba_ravi]

=IF(ISBLANK($A70),"",VLOOKUP($A70,'2015Quotes'!$A$2:$J$1000,COLUMN(B1),FALSE))

Please Login or Register  to view this content.

Try this and copy

[Tony Valko]

Try this...

=IF($A70="","",VLOOKUP($A70,'2015Quotes'!$A$2:$J$1000,COLUMNS($A1:A1)+1,0))

[Brett1966]

Brilliant, worked perfectly. You saved me a lot of heartache. Much appreciated.

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.

[Tony Valko]

Using COLUMN(B1) leaves the formula vulnerable to new column insertions.

Insert a new column A then COLUMN(B1) becomes COLUMN(C1) and the formula no longer returns the data from column 2 of the lookup table.

Using the COLUMNS( ) function can account for this.