VLOOKUP -- automatically change col index

Assuming the first instance of the formula is in D2 then,rather than use a number for the column index, e.g. 2, use a function like

COLUMNS(D$2:D2)+1

when this is copied across to E2 it automatically increments.

I prefer to use INDEX(MATCH type formulas here, then you can just make the references relative or fixed as you need, what's your current formula?

Originally Posted by ephilli2

Hi, daddylonglegs,

Thanks! I figured it out with your suggestion.

My original formula: =VLOOKUP($J$1,$A$1:$D$6,2,FALSE)

Modified formula: =VLOOKUP($J$1,$A$1:$D$6,COLUMNS($A$1:B6),FALSE)

Now, when I copy the formula to the right, the relative cell reference in the COLUMNS formula updates to include the additional column(s).

I was trying to figure this one out as I have data that is trying to divide one value on one spreadsheet from one value on another spreadsheet. So my formula has to look up the item # on my current to the same item number on the other spreadsheet then it has to go X(37) number of rows on the other workbook to the right once it finds that item # and then perform the equation. From there it moves one column to the right right after this.

I could not find an equation for this was anyone able to figure it out?

=(AZ4/(VLOOKUP($A4,'[Eligard Inserts Working Spreadsheet 11-16-10.xls]INSERTS'!$A$4:$AV$94,37,FALSE)))

This pulls in the number from column 37and divides az4/(number from column 37 that matches to A4).

This works on one cell but not when you copy and paste this to the right. My solution that I prefer not to use is:

=(AZ4/(VLOOKUP($A4,'[Eligard Inserts Working Spreadsheet 11-16-10.xls]INSERTS'!$A$4:$AV$94,'[Eligard Inserts Working Spreadsheet 11-16-10.xls]INSERTS'!AK$1,FALSE)))

What I did was make another row and on column37 in this blank row I wrote 37,38,39... Then this formula above returns a value of 37 and moves on. The above formula is a simplistic solution but is unrealistic when you have a larger spreadsheet. Does anyone have any ideas?