Vlookup, Column Index Num and Autofill

  • Thread starter Thread starter mp
  • Start date Start date
M

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)
 
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
 
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
 
The lookup is pointing to another worksheet and the columns, of course, don't
line up for this formula to work correctly.
 
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.
 
History1!$B$13:$IV$1499
The info I'm looking for is in S13
Click to expand...

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.
Click to expand...

Make sure you have calculation set to automatic.

Tools>Options>Calculation tab>Automatic>OK
 
Back
Top