modifying columns in vlookups easily

[bigdan]

I just had to set up a vlookup formula for Jan to Dec. Was kinda annoying changing the numbers for each month, like writing vlookup(A1,B100:M200,1,false) and changing the 1 to 2 to 3 etc.


I realize we can create a helper row with 1-12 hidden somewhere and link the 1 in the formula to that instead. I dont mind doing that temporarily but I would like those numbers to get embedded in.

Is there a way to do this?

 

[AliGW]

Try this:

=VLOOKUP(A1,B100:M200,TEXT(NOW(),"m"),FALSE)

It will use the number of the current month.

 

[T. Valko]

Are you copying the formula down a column or across a row?

What is the cell address of the first cell that contains the formula?

 

[bigdan]

I'm copying from left to right. The first cell would be B1.

 

[T. Valko]

Try it like this...

=VLOOKUP($A1,$B100:$M200,COLUMNS($B1:B1),0)

 

[AliGW]

If this isn't what you are looking for:

=VLOOKUP(A1,$B$100:$M$200,TEXT(NOW(),"m"),FALSE)

try this:

=VLOOKUP(A1,$B$100:$M$200,column()-1,FALSE)

The column()-1 statement is returning the column number of the column the cell is in (B=2) and taking 1 from it. When you drag this across and down, the result of that statement will increase by 1 each column you go across. Hope this makes sense!

 

[Jonmo1]

I'm a big fan of using Index/Match in this situation.
Givin this example vlookup from the original post
vlookup(A1,B100:M200,1,false)

Try
=INDEX(B$1:B$100,MATCH($A1,$B$1:$B$100,0))

Drag it to the right.

 

[T. Valko]

=VLOOKUP(A1,$B$100:$M$200,column()-1,FALSE)

That doesn't work if I enter the formula in cell GH27!

Using COLUMN()-1 is dependent upon the cell the formula is entered in.

Using the COLUMNS(...) and/or ROWS(...) functions are more robust.

 

[AliGW]

That doesn't work if I enter the formula in cell GH27!

I know how it works and I explained it at the bottom of that post:

The column()-1 statement is returning the column number of the column the cell is in (B=2) and taking 1 from it. When you drag this across and down, the result of that statement will increase by 1 each column you go across. Hope this makes sense!

In this case, we know that the formula will be put into B1 and copied across and down.

It's one perfectly valid way of doing it. There are many!

 

[Marcelo Branco]

In this case, we know that the formula will be put into B1 and copied across and down.

I think that even knowing the formula will be put in column B it is recommended and more robust to use COLS($B1:B1)
Why? Suppose that, for some reason, a new column is inserted to the left of column B.

M.