Using LOOKUP functions to give AVERAGE range

[stickywicket]

Hi everyone - I've seen a few posts on various forums with questions that initially seem similar to mine, but the given answer doesn't help me (e.g., here). I'm using XL2007 with Windows XP. Here's my issue:

I have a table of values with a header row (the header row is a series of time points - 0, 4.5, 9, 13.5, 18, etc.). Also, I have two cells that I am referencing for start and stop time points (so, let's say I want to use 4.5 and 13.5). Ideally, my formula would be able to use a time point for an HLOOKUP in the table and then drop down to the appropriate row to mark that cell as the part of the AVERAGE function range. My first attempt looks something like this:

=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))

Excel doesn't like the above formula and my guess is because the HLOOKUP gives me values not cell addresses. Is there a function I can insert to get the cell address from the HLOOKUP? I tried to incorporate the ADDRESS and MATCH functions unsuccessfully. But I'm not even sure if those are what I need.

In the end, I want to be able to change the time points in the referenced cells (for this example, B2 and B3) and have my table of calculated values adjust their averages based on the new window of time points given.

Define my average window:
B2=4.5
B3=9

My table of averages:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,3,FALSE):HLOOKUP($B$3,$D$5:$M$17,3,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,4,FALSE):HLOOKUP($B$3,$D$5:$M$17,4,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,5,FALSE):HLOOKUP($B$3,$D$5:$M$17,5,FALSE))
etc...

Thanks for the help! Please let me know if something isn't clear.

 

[shift-del]

Hello

=AVERAGE(INDEX(D6:M6,,MATCH($B$2,D$5:M$5,0)):INDEX(D6:M6,,MATCH($B$3,D$5:M$5)))

 

[stickywicket]

Thanks, shift-del! You rock!