Excel macro/function help needed

[MrBond]

I have a spreadsheet I'm working on, it's a template for some fairly complex data that gets filled in from another program.

What I'd like to do is have user-defined averages of that data. Say we want to average from points 1-10 one day and 25-75 the other, we don't have to change the entire template, just the cells that say what to average.

Is there some function that will look up a value in one column, then return the address of the cell next to it? For example, I'd like to find the row of data that has the 5th point in it, then return the address of the cell that contains the fifth point (B5 in this case). I could hopefully put that forumula into the AVERAGE function.

Is that even possible? I've looked at a lot of the functions, VLookup almost does what I need, but it returns the contents of the cell, not the address.

 

[TSDible]

Here is what I came up with

=ADDRESS(ROW(INDEX(A2:A7,MATCH(E1,A2:A7))),COLUMN(INDEX(A2:A7,MATCH(E1,A2:A7)))+1)

A2:A7 -- Range that I searched for data
E1 -- Cell that contained the value I wanted to look up
+1 at the end returns the address of the cell to the right of the cell I looked up. (-1 would return the address of the cell to the left)

That returns a value such as $B$4

If you want a relative address such as B4, use the following

=ADDRESS(ROW(INDEX(A2:A7,MATCH(E1,A2:A7))),COLUMN(INDEX(A2:A7,MATCH(E1,A2:A7)))+1,4,TRUE)

hope that helps