How to write Vlookup to drag across a row?

  • Thread starter Thread starter J.Mart
  • Start date Start date
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?
 
=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
 
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
 
=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
 
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)
 
Back
Top