Excel has stopped adding numbers when extending a formula to adjacent cells

Hello! For some reason Excel isn't autofilling the formula for me. This is what I am using:

=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)

When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.

I can't keep going manually as I need to go to about 300 cells...

Any ideas as to why this is happening and how to fix it?

Thanks!

Sorry I wasn't clear. I usually use just one cell and I have tried it in this but it is still pasting the value exactly as the previous cell. So I selected multiple cells to try and make Excel see a pattern, yet it just repeated itself..

I don't know what's going on.

Try using a horizontal incrementer for the 3rd param
eg: COLUMNS($A:A)+22 to replace the "23" in your vlookup
=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)

viz, Try it like this in the startcell:
=VLOOKUP($A5,TEAM!$B:$MZ,COLUMNS($A:A)+22,FALSE)
Now it should increment properly as you copy across

And if you need to increment it as you copy DOWN,
use eg: ROWS($1:1)+22
--------------------
Any good? Wave it, hit the little star at the bottom left of my responses

No, the formula I first pasted was taken directly from my workbook.

=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)

The idea is to match the value in A5 on my current worksheet, to a range on a second worksheet (TEAM), the range is B:MZ. This is why I have $A and $B:$MZ, as I don't want the ranges to change.

I am trying to change the column value automatically 23 > 24 > 25 by dragging the format button. However when I do it returns the exact same formula with column 23 in my range as the target column.

I have put a single "1" in a blank cell on the same sheet and dragged it and Excel calculates it fine, so it has to be something to do with my formula or something.

Oh OK I see it now, VLOOKUP doesn't calculate columns automatically? I have never seen the COLUMN formula before. Now I have it working, thank you all!

I tried using Index/Match the last time I did something similar.. my computer simply couldn't handle the large numbers of processes, Excel just wouldn't respond. Now I find VLOOKUP much quicker and less intensive.