When dong VLOOKUP, when I drag the formula down, I want the COLUMN Lookup Value to change

Hi,
I have a very complicated looking spreadsheet that I want to change from horizontal columns of data into a vertical spreadsheet. I figured that the best way to do this is to do a vlookup to return the values I'm looking for. I've concatenated the 2 criteria that needed to be met but when I perform the VLOOKUP, I realized that I want my LOOKUP VALUE to shift one to the right (on the table array) when I drag the formula ONE cell down.

I googled this and discovered OFFSET and COUNT as nested formulas but I cannot get it to work.

I know I'm kinda close but I don't know what I'm missing. I've added a very trivial spreadsheet to explain my dilemna...hopefully it has enough info.

Just imagine a very large spreadsheet with this issue....

Any help would be greatly appreciated.

Please try at
A50
=INDEX($A$3:$A$7,CEILING(ROWS(A$50:A50)/COLUMNS($B$2:$F$2),1))&INDEX($B$2:$F$2,MOD(ROWS(A$50:A50)-1,COLUMNS($B$2:$F$2))+1)

B50
=INDEX($G$46:$K$46,MATCH(A50,$A$46:$E$46,))


drag A50:B50 down

In A60

Please Login or Register  to view this content.

In B60

Please Login or Register  to view this content.

In C60

Please Login or Register  to view this content.

Copy down all