Vlookup, Column Index Num and Autofill

[mp]

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)

 

[Sheeloo]

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

 

[T. Valko]

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

 

[mp]

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

 

[mp]

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

 

[T. Valko]

Just add the sheet name:

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

 

[mp]

using your format here's the actual formula:
=IF(ISERROR(VLOOKUP($B19,History1!$B$13:$IV$1499,COLUMNS($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]

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.

Tools>Options>Calculation tab>Automatic>OK

 

[mp]

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

MP

 

[T. Valko]

You're welcome. Thanks for the feedback!