Vlookup - to change column lookup automatically

[CDNWolf]

Hi guys,

Couldn't find an answer online so here I am...

All I want is to copy this cell down or across to automatically look in the next column without manually typing it out
I have over 150 columns to look at so not ideal to change the column number manually for each formula

This is what I am doing manually.
=VLOOKUP(Sheet1!$A$2,Range2,2,FALSE)
=VLOOKUP(Sheet1!$A$2,Range2,3,FALSE)
=VLOOKUP(Sheet1!$A$2,Range2,4,FALSE)
=VLOOKUP(Sheet1!$A$2,Range2,5,FALSE)
=VLOOKUP(Sheet1!$A$2,Range2,6,FALSE)

Not sure if Match or Column can be used in this formula.
FYI Range2 = =$C$4:$DV$13 if that helps

Thanks in advance for your help

 

[RoryA]

If you want to copy it down you could use ROW() for the column number (adjust as needed depending on location of the formula) and to copy across you could use COLUMN(). It would be more efficient to use INDEX and MATCH so you only do the lookup once.

 

[CDNWolf]

If you want to copy it down you could use ROW() for the column number (adjust as needed depending on location of the formula) and to copy across you could use COLUMN(). It would be more efficient to use INDEX and MATCH so you only do the lookup once.

Can you plug in your suggestion, so I see what you mean?

 

[RoryA]

So one MATCH formula to lookup the value:

=MATCH(Sheet1!$A$2,$C$4:$C$13,0)

let's say that is in B2. Then multiple index formulas use that value directly:

=INDEX(D$4:D$13,$B$2)

and just copy across as needed.

 

[CDNWolf]

So one MATCH formula to lookup the value:

=MATCH(Sheet1!$A$2,$C$4:$C$13,0)

let's say that is in B2. Then multiple index formulas use that value directly:

=INDEX(D$4:D$13,$B$2)

and just copy across as needed.

I did find a way around it...

In Column B (on a deferent sheet, I numbered the rows down from 1 - 150 and used that reference in my formula;
=VLOOKUP(Sheet1!$A$2,Range2,B2,FALSE) Because B2 = 1, it will look at column 1 in Range2
=VLOOKUP(Sheet1!$A$2,Range2,B3,FALSE) Because B3 = 2, it will look at column 2 in Range2
=VLOOKUP(Sheet1!$A$2,Range2,B4,FALSE) Because B4 = 3, it will look at column 3 in Range2

Thanks again for your suggestion. I couldn't be bothered to manually change the column numbers manually for each lookup, so time consuming

 

[Automatrix]

What are you looking to accomplish with your VLOOKUP formulas? There may be a better option.

 

[CDNWolf]

What are you looking to accomplish with your VLOOKUP formulas? There may be a better option.

I'm sure there is another solution but as mentioned in my last reply, my solution worked well.

Cheers

 

[RoryA]

I couldn't be bothered to manually change the column numbers manually for each lookup, so time consuming

My suggestion didn't require that and would have avoided looking up the same information repeatedly as your current version does.