This feels like a common question but I cannot get it to work or find an absolute answer:

How to make the column_index "auto-increment" as I drag horizontally the cell with this VLOOKUP formula?

```
=VLOOKUP(input!$B$3,data!$A4:$AJ52,2)
```

My lookup tables has 30 to 50 columns each. If auto-incrementing the column index is possible then it will be a great help.

## Best Answer

One solution is to use

`INDEX`

and`MATCH`

rather than`VLOOKUP`

. This is more robust and arguably clearer,e.g. use this formula copied across.`=INDEX(data!B4:B52,MATCH(input!$B$3,data!$A4:$A52))`

That formula matches in col A and returns a value from col B as does your

`VLOOKUP`

, but when you copy across that changes.Note that I used

`MATCH`

with no third argument to be consistent with your VLOOKUP with no fourth argument.Because the same

`MATCH`

function is repeated across the whole row then for more efficiency you could put that function alone in one cell then refer to that same cell across the row