 # How to Use a Cell Value as the Column Index in a VLOOKUP Formula?

• Is it possible, in a VLOOKUP formula, to use the value that is in a cell as the column index?
Specifically, I have a VLOOKUP formula in cell R1001: =VLOOKUP(\$Q1001,\$A\$16:\$DZ\$950,47,0). The formula is then repeated down column R for (currently) 283 rows.
I want to replace the "47" with the value that is in cell K999, that value in turn being derived from an underlying formula. The underlying formula is based on a value that I enter in cell J999. My objective is to be able to change the value in cell J999, which will then give a new, calculated value in K999, which in turn will then give me new output values from the 283 instances of the VLOOKUP formula in col R.

• Re: How to Use a Cell Value as the Column Index in a VLOOKUP Formula?

Try

=VLOOKUP(\$Q1001,\$A\$16:\$DZ\$950,\$K\$999,0)

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: How to Use a Cell Value as the Column Index in a VLOOKUP Formula?

Hi KjBox
Prior to posting the query I had actually tried that and found that it didn't work, but your suggestion to try it meant that it should work so I've tried it again. The result was initially puzzling.
The formula in K999 is basic: =23+(8*J999) so each increment in J999 moves me across the worksheet by 8 columns. I'd been doing all my pre-post testing with a value of 4 in J999, which equates to col 55. This returns a VLOOKUP error: #REF! yet the cell in col 55 (col BC) referenced by Q1001 contains the value 2.0.
However, if I reduce the J999 value to 3, the VLOOKUP formula works fine and returns, correctly, a value of 1.7. Similarly, a value of 2 returns the correct figure and so does a value of 1. But - once I use a value of 4 or greater in J999 I get the #REF! error.
I've just rechecked to ensure that the formula is giving the correct column index, which it is, however I've now found the source of the problem. Somehow, during my earlier testing, I had inadvertently changed the array in the VLOOKUP formula to \$A\$16:\$AZ\$1000 instead of \$DZ\$1000, so of course any column index above 52 returned an error.
Consequently, with some embarrassment I have to admit that the problem in fact isn't a problem, rather it's the result of my carelessness.
Thank you for the guidance, and for prompting me to go back and look more carefully at what I'd typed.

• Re: How to Use a Cell Value as the Column Index in a VLOOKUP Formula?

You're welcome, pleased you were able to find the error/solution.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!