Replicating VLOOKUP 4

[lole]

Hi Again

Now that xlbo has sorted out my table reading problem (thanks!!!!) I have another.....

How do I paste/copy/replicate a VLOOKUP cell without the table arrange incrementing but with the reference cell incrementing. I need to get VLOOKUP into about 10,000 cells and keep it looking at the same range but seeded by another cell in the same row as the VLOOKUP.

Or to put it another way, how do I copy VLOOKUP with a relative lookup cell value and an absolute range?

I've tried any combination of dragging, copying, pasting and played with the format painter but I can't get both!

Any thoughts would be much appreciated.


Lole

 

[DaleWatson123321]

The simple method is to assign a Range Name to the Table.

Assigning a Range Name involves:

1) Highlight the range (table in this case)
2) Hold down the <Control> key and hit <F3>
3) Type the name
4) Hit <Enter>

By using a Range Name, the reference in the formula will stay ABSOLUTE.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

I know Dale likes Range names and I agree that they're a good way to go but this seems like a good opportunity to let Lole know about relative and absolute cell referencing

Consider the formula = vlookup(A1,B2:Z1000,2,false)
when you drag this down, the next cell will look like

=vlookup(B1,B3:Z1001,2,false)

To obtain what you want, you would use
=vlookup(A1,$B$2:$Z$1000,2,false)
putting a $ in front of a Row or Column reference, means it is &quot;Absolutely&quot; referenced ie even when you copy / move / drag the formula, it will always reference that row or column

In this instance, because there is no right or left movement, you could actually use
=vlookup(A1,B$2:Z$1000,2,false) and you would get the same results. Personally, I would go with Dale's suggestion as it is good to get used to using range names, but there are occasions when it's good to know about relative and absolute referencing.
HTH
Geoff

 

[lole]

Thanks Dale and Geoff

You have no idea how much work and stress you have saved me from. I'm always willing to learn but pressures make that a very difficult think to do by yourself sometimes!

Lole