Complicated lookup - file attached

**Tnesper** · 06-25-2008, 01:28 PM

I know I'm crowding the forum, but I'm an intern who is learning a lot, so here we go.

Attached is an income statement I am working on. I have minimized the data and changed the numbers in order to prevent myself from getting fired.

As it shows, in columns B to I you can select which column you would like to analyze and compare to the other selections. Then, a simple hlookup function displays the appropriate operating expense based on what you have selected. But what I can’t get excel to do, is display the appropriate YTD % that also goes with the selection you make in the top row. It should make more since when you look at the file.

For example, I know that what I want excel to do is… in cell C4 I want it to display whatever is in the column next to the column where the data in cell B4 was found. I know its confusing, hopefully it will make more since.
Like I said, this is a minimized version. As the formulas in the yellow columns show, the original data extends to DM57.

**NBVC** · 06-25-2008, 01:45 PM

tRY:

=INDEX($J$1:$DM$57,ROW(),MATCH($B$1,$J$1:$DM$1,0)+1)

Copied down.

Btw: You can also copy down your HLookup() formula, if you replace the hardcoded row number with the ROW() function...

E.g. =HLOOKUP($B$1, $J$1:$DM$57, ROW(), FALSE) copied down.