Vlookup, Column Index Num and Autofill
 

If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I
autofill across. It appears as though the column index number is always an
absolute value.

Example
Column A Column B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

Use the formula in Col B
=Vlookup($A3,$C$1:$F$12,COLUMN(),false)

If the formula is in Col B then it is same as
=Vlookup($A3,$C$1:$F$12,2,false)

since COLUMN() evaluates to 1 (in A), 2 (in B), 3 (in C), ... depending upon
the column the formula in is

btw
Vlookup($A3,$C$1:$F$12,1,false) should give you circular reference error if
enterd in Col A
"mp" wrote:

If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I
autofill across. It appears as though the column index number is always an
absolute value.

Example
Column A Column B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I
autofill across. It appears as though the column index number is always
an
absolute value.

Example
Column A Column
B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

The lookup string is looking for data on another worksheet. This formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I
autofill across. It appears as though the column index number is always
an
absolute value.

Example
Column A Column
B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

The lookup is pointing to another worksheet and the columns, of course, don't
line up for this formula to work correctly.

"Sheeloo" wrote:

Use the formula in Col B
=Vlookup($A3,$C$1:$F$12,COLUMN(),false)

If the formula is in Col B then it is same as
=Vlookup($A3,$C$1:$F$12,2,false)

since COLUMN() evaluates to 1 (in A), 2 (in B), 3 (in C), ... depending upon
the column the formula in is

btw
Vlookup($A3,$C$1:$F$12,1,false) should give you circular reference error if
enterd in Col A
"mp" wrote:

If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one as I
autofill across. It appears as though the column index number is always an
absolute value.

Example
Column A Column B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

Just add the sheet name:

=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0)


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
The lookup string is looking for data on another worksheet. This formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one
as I
autofill across. It appears as though the column index number is
always
an
absolute value.

Example
Column A
Column
B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE)))

Problem - no matter what range I put in it always returns the same value.
The info I'm looking for is in S13. The info it's pulling comes from A13.

I appreciate the help.

"T. Valko" wrote:

Just add the sheet name:

=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0)


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
The lookup string is looking for data on another worksheet. This formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across multiple
columns, how do get the column index num reference to increase by one
as I
autofill across. It appears as though the column index number is
always
an
absolute value.

Example
Column A
Column
B
Vlookup($A3,$C$1:$F$12,1,false) Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

History1!$B$13:$IV$1499
The info I'm looking for is in S13

Ok, but that's not what you demonstrated in your original post. You
demonstrated that you want the results starting from the 1st column of the
lookup table and then incrementing as you copy across. S13 would be column
number 18 *relative* to your lookup table.

If you want the first result to come from column 18 of the lookup table:

=IF(COUNTIF(History1!$B$13:$B$1499,$B19),VLOOKUP($ B19,History1!$B$13:$IV$1499,COLUMNS($B:S),0),"")

The first result will come from column S. As you copy across the results
will come from columns T, U, V, W, etc., etc.

no matter what range I put in it always returns the same value.

Make sure you have calculation set to automatic.

ToolsOptionsCalculation tabAutomaticOK


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE)))

Problem - no matter what range I put in it always returns the same value.
The info I'm looking for is in S13. The info it's pulling comes from A13.

I appreciate the help.

"T. Valko" wrote:

Just add the sheet name:

=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0)


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
The lookup string is looking for data on another worksheet. This
formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across
multiple
columns, how do get the column index num reference to increase by
one
as I
autofill across. It appears as though the column index number is
always
an
absolute value.

Example
Column A
Column
B
Vlookup($A3,$C$1:$F$12,1,false)
Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

Thanks - worked like a charm. Now all I have to do is understand what you've
done.

MP

"T. Valko" wrote:

History1!$B$13:$IV$1499
The info I'm looking for is in S13

Ok, but that's not what you demonstrated in your original post. You
demonstrated that you want the results starting from the 1st column of the
lookup table and then incrementing as you copy across. S13 would be column
number 18 *relative* to your lookup table.

If you want the first result to come from column 18 of the lookup table:

=IF(COUNTIF(History1!$B$13:$B$1499,$B19),VLOOKUP($ B19,History1!$B$13:$IV$1499,COLUMNS($B:S),0),"")

The first result will come from column S. As you copy across the results
will come from columns T, U, V, W, etc., etc.

no matter what range I put in it always returns the same value.

Make sure you have calculation set to automatic.

ToolsOptionsCalculation tabAutomaticOK


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE)))

Problem - no matter what range I put in it always returns the same value.
The info I'm looking for is in S13. The info it's pulling comes from A13.

I appreciate the help.

"T. Valko" wrote:

Just add the sheet name:

=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0)


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
The lookup string is looking for data on another worksheet. This
formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across
multiple
columns, how do get the column index num reference to increase by
one
as I
autofill across. It appears as though the column index number is
always
an
absolute value.

Example
Column A
Column
B
Vlookup($A3,$C$1:$F$12,1,false)
Vlookup(($A3,$C$1:$F$12,2,false)

Vlookup, Column Index Num and Autofill
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
Thanks - worked like a charm. Now all I have to do is understand what
you've
done.

MP

"T. Valko" wrote:

History1!$B$13:$IV$1499
The info I'm looking for is in S13

Ok, but that's not what you demonstrated in your original post. You
demonstrated that you want the results starting from the 1st column of
the
lookup table and then incrementing as you copy across. S13 would be
column
number 18 *relative* to your lookup table.

If you want the first result to come from column 18 of the lookup table:

=IF(COUNTIF(History1!$B$13:$B$1499,$B19),VLOOKUP($ B19,History1!$B$13:$IV$1499,COLUMNS($B:S),0),"")

The first result will come from column S. As you copy across the results
will come from columns T, U, V, W, etc., etc.

no matter what range I put in it always returns the same value.

Make sure you have calculation set to automatic.

ToolsOptionsCalculation tabAutomaticOK


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,C OLUMNS($S13:S13),FALSE)),"",(VLOOKUP($B19,History1 !$B$13:$IV$1499,COLUMNS($S13:S13),FALSE)))

Problem - no matter what range I put in it always returns the same
value.
The info I'm looking for is in S13. The info it's pulling comes from
A13.

I appreciate the help.

"T. Valko" wrote:

Just add the sheet name:

=VLOOKUP($A3,Sheet2!$C$1:$F$12,COLUMNS($A1:A1),0)


--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
The lookup string is looking for data on another worksheet. This
formula
didn't work.

"T. Valko" wrote:

Let's assume you enter the first formula in cell A1.

=VLOOKUP($A3,$C$1:$F$12,COLUMNS($A1:A1),0)

Copy across as needed

--
Biff
Microsoft Excel MVP


"mp" wrote in message
...
If I have a vlookup in one cell and I try to autofill across
multiple
columns, how do get the column index num reference to increase by
one
as I
autofill across. It appears as though the column index number is
always
an
absolute value.

Example
Column A
Column
B
Vlookup($A3,$C$1:$F$12,1,false)
Vlookup(($A3,$C$1:$F$12,2,false)