Lookup - drag values

Good Afternoon,

I am using Excel 365 and have the following formula that I wish to drag so that the column numbers increase. However, I cannot get this to work without manually changing the formula. Can anyone help or advise on a revised formula please? I have trawled the forum for an hour and probably just confused myself

=VLOOKUP($C$6,Combined!$A:$GH,2,0)

maybe this will help if you write the value like this... $C$6 that locks in cell c6 and regardless of which way, up down right left the formula will stay locked on C6.
if you do C$6 then it locks in row 6 but when you drag it right it will index to D$6 then E$6 etc and B$6 if you drag it left but will stay on 6 as you drag up or down.
if you do $C6 then it locks on column C but will move to row 5 or 4 or 7 or 8 when you drag up or down and of course C6 will advance or decrease in any direction you drag the formula. The same rule applies to whole columns except since you are not naming a row it only applies to the column. Hope that helps you.

Oh and BTW, in a vlookup statement like ... =VLOOKUP($C$6,Combined!$A:$GH,2,0), the 2 after the $A:$GH means that it will ONLY look at the information in column C so all the remainder of the columns out to GH are wasted since the 2 limits it to looking only down column C.

If you write the following formula in a cell in column B you will get the number of that column, which is 2:

Please Login or Register  to view this content.

If the cell where the VLOOKUP formula is placed is, say, F3, you could adapt that to obtain the number 2 for your first VLOOKUP formula.
Since column F is column number 6, for the first VLOOKUP formula you would type COLUMN()-4 to obtain the desired "2" in your initial formula.
Then when you drag to the column next to it, the G column number will be 7, minus 4, it will be 3. Etc.

So you could change your formula to something like this (assuming your formula is in column F):

Please Login or Register  to view this content.

which column are you looking to change, the vlookup for C6 or the A:GH or the reference value of 2?
this would be one example of incorporating a changing column reference into your formula ...=VLOOKUP($C$6,Combined!$A:$GH,COLUMN(B1),0)
that way it will look initially at column C but when dragged right it will next index to D then E etc. Is that what you are wanting?
if not please indicate which column you are looking to index, and a sample workbook with some expected results would be helpful.