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




  • 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... :)




    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...


    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!