[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!

    ooops, my bad.


    ="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!

Participate now!

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

Register Yourself Login