Using a dynamic column number for a vlookup

0
votes

My spreadsheet has an Allocated and an Actual column for each month. As the final report is run and the month's change, I want to have my vlookup automatically use the value of the month the report is run for (entered in address 'z1') as the column to pull the data from.

For example:

z1 = 02 (for running the report in Feb)

a3 = ID

b3 = Jan Allocated $

c3 = Jan Act $

d3 = Feb Allocated $

e3 = Feb Act $

a10 = vlookup('ID123',A:E,2,false)

where the 2 in the vlookup formula actually references the address 'Z1' where the column number is stored (this will eventually be a calculated number based on the month and the offset of where the values start given all other data in the worksheet). This will then change as the value in 'Z1' changes without the need to change all the formulas each month.

I envisioned it to look something like: =vlookup('ID123',A:E,z1,false)

I get a #ref error when I use the above. Thanks

2
excelvlookup
use double quotes not single quotes. – Scott Craner
And make sure Z1 is a number and not text that looks like a number. – Scott Craner

2 Answers

0
votes

If your Z1 entry of "02" (February) means you want to return column D (Feb Allocated) then use:

=vlookup("ID123",A:Y,z1*2,false)

Otherwise if you want to return column E (Feb Act) then use:

=vlookup("ID123",A:Y,z1*2+1,false)

That means Z1 can always contain the month number 01-12, and the little bit of maths in the vlookup will reinterpret to the required the col_index_num. I also changed the data table range from A:E to A:Y to include the full 12 months range... bit of a guess.

0
votes

You can use the MATCH(lookup_value, lookup_array, match_type) function concatenated with " Allocated $" or " Act $" to find the column number.

=VLOOKUP("ID123",A:E,MATCH(Z1&" Act $",3:3,0),FALSE)