Returning a cell reference to include in a forumula

[Hesh]

I would like to lookup a value and return the cell reference for that matching value and then use that reference in a sum formula. I would like to include it all in one forumula.

Any help would be appreciated.

Many Thanks

Hesh

[Pete_UK]

Well, that doesn't give us a lot to go on, so do the research yourself - use XL Help to look for MATCH, ADDRESS, INDIRECT, INDEX, all of which might be used in this case.

Pete

[Hesh]

Sorry wasn't meaning to be lazy, but I am guilty of not providing information.

I had already attempted to use the match function which did return the row number that I required, I then attempted to concatente the return row number in to a sum formula which didn't work example

=SUM(A47concatenate("A",MATCH(C2,'Period Info'!B:B,'Period Info'!A:A))))

Thinking that the concatenate was only a text thing, I was then stumped as to how to get the A column reference (which is know) to merge with the returned row reference.

For information the A47 cell reference is a substitute linked cell (for ease of creating the forumla) and I hope to use a correct formula on both sides of the sum : , because both sides will rely on the returned cell reference.

Please dont laugh at the concatenate attempt, I was trying :-)

[Pete_UK]

Well, assuming that the A47 is fixed for now, you could use this:

=SUM(INDIRECT("A47:A"&MATCH(C2,'Period Info'!B:B,0)))

assuming that with your MATCH function you want the row where C2 can be found in column B of the Period Info sheet.

You can do a similar thing to get rid of the 47, i.e.:

=SUM(INDIRECT("A"&new_match_function&":A"&MATCH(C2,'Period Info'!B:B,0)))

Hope this helps.

Pete

[Hesh]

Thanks for this Pete, worked just as you stated.

Sorry for the delay replying, really appreciated.

Hesh