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


"J.Mart" wrote in message
...
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?

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

On Dec 10, 10:21 pm, J.Mart wrote:
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,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


--
Biff
Microsoft Excel MVP


"J.Mart" wrote in message
...
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?

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"J.Mart" wrote in message
...
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?