# [Solved] Formulas: How to return a cell address or row from

• I'm trying to return a cell address from a vlookup function so that I can use offset to calculate the average of the cells following the first value that it finds with the vlookup.

In other words, I am looking for the first 2 in a column and want to sum the values4 columns over and 40 rows down. I know I can use offset, i just need to get the first cell address by formula.

This is only for formula within excel. Thanks for the help!

• Here is one approach using =match instead of vlookup:

="a"&MATCH(2,a10:a100,0) or
="a"&MATCH(2,rangename,0)
where you are searching for "2" in range "rangename". This example ssumes that rangename in column A. 0 indicates that we are looking for an exact match.

• How about if the cell i'm trying to find is on another sheet. I can't get the sheet reference to work with this. Thanks for the help!

="a"&MATCH(2,rangename,0)+CELL("row",rangename)-1

or

="a"&MATCH(2,Sheet2!A10:A100,0)+CELL("row",Sheet2!A10:A100)-1

• what does that do? from what I see, it makes the format a number instead of general...

Here's the problem...maybe this will help...

I'm on sheet x and trying to make a formula that will find the appropriate value in A:A on the average sheet, then offset 4 columns and size it 50 high, then average those numbers. So the offset value looks like: average(offset(x,Average!A:A,0,4,50,1)). This works when i do it manually, but then I try and use the match to find the row number of the first cell of my offset and it won't do it. I hope that makes sense. I guess it might be a typo in the format i'm trying to use or the way i'm trying to write it. I hope this helps you to understand what I'm trying to do... Thanks for the help!

• OK, I think I have it now, I hope so, let me know...

=AVERAGE(OFFSET(INDIRECT("average!a"&MATCH(2,average!A1:A100,0)+CELL("row",average!A1:A100)-1),0,4,50,1))

or

=AVERAGE(OFFSET(INDIRECT("average!a"&MATCH(2,rangename,0)+CELL("row",rangename)-1),0,4,50,1))

in the top example if a 2 is in a1 then it will average(average!e1:e50)
in the top example if a 2 is in a2 then it will average(average!e2:e51)

• I just got it before you replied. Yes, it does require an indirect, but i don't know why cause it's not referencing another cell, but rather the cell that we put together. Also, you don't need the +Cell protion of it. The first half works just fine. So it reads...

=AVERAGE(OFFSET(INDIRECT("Average!A"&MATCH(\$A6,Average!\$A:\$A,0)),0,4,50,1))

Thanks for your help! I really appreciate it. Do you know why it requires the indirect? Thanks again!

• A bit slow, but might the attached takes a different approach to geting the target address. Include it in your table.

• Thanks for the help everyone!!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!