How to write Vlookup to drag across a row?

J

J.Mart

How do you write a Vlookup function so that you can drag across a row and the
reference column will change automatically...so it goes from
=Vlookup(A1,A10:A20,2,false)
=Vlookup(A1,A10:A20,3,false) automatically?
 
H

Harald Staff

=Vlookup(A1,A10:A20,COLUMN(),false)
but it requires more calculation, so make sure your ease of dragging is
worth it.

HTH. Best wishes Harald
 
P

Pete_UK

You don't want the cell addresses to change, so you have to make these
absolute (at least the column part). Also, you want the 2 to change to
a 3, then to 4 etc, so here you can make use of the COLUMN function.
This will give you:

=Vlookup($A1,$A$10:$Z$20,COLUMN(B1),false)

Drag this across the row, and then down if you want to.

Note that I have changed your table reference to cover A to Z, which
means that you can copy this out to column Z if your table really is
that wide (but you just had a one-column table in your example, so
that wouldn't have worked).

Hope this helps.

Pete
 
T

T. Valko

=Vlookup(A1,A10:A20,2,false)

Well, you have a problem in that your lookup table only refers to a single
column!

Try it like this:

Assuming you enter the formula in cell F1.

=VLOOKUP($A1,$A10:$D20,COLUMNS($F1:G1),0)

COLUMNS($F1:G1) evaluates to 2

As you copy across this will increment accordingly: 3, 4
 
R

RagDyer

You realize that your example formulas wouldn't work at all, since your
range is a single column.

Revising your range references, try this:

=VLOOKUP($A1,$A10:$K20,COLUMNS($A:B),0)
 
Top