Like ADDRESS function but returning a range of cells?

[Perey]

I want to do the following:

1. Take a sheet name
2. Search a range on that sheet (same range on each sheet) for a given value
3. Get a value on that sheet, in the found row, from a certain column

Steps 2 and 3 alone are straightforward (at least, in that I figured out the process by a combination of the docs and a Q&A here on Ask LibreOffice):

=INDIRECT(ADDRESS(MATCH($A1,Foo.$A1:$A10,0),3,1,1,"Foo"))

(In this example, the search key for MATCH is in column A, both on this sheet and on sheet "Foo". The value sought is in column C, hence the column argument is 3.)

But not hard-coding the sheet name "Foo" is proving challenging, because the ADDRESS function only returns a single cell, not a range like Foo.$A1:$A10.

My best work-around is this ugly thing:

=INDIRECT(ADDRESS(MATCH($A1,INDIRECT(ADDRESS(1,1,1,1,$B1)&":"&ADDRESS(10,1,1,1,$B1)),0),3,1,1,$B1))

(Now, the sheet name to search is in column B.)

Is there a better way to do it?

[keme]

Where I see INDIRECT(ADDRESS(...);...) constructs, I usually look for a way to replace it with OFFSET(). To me it looks like that is not possible in this case, but you can still use OFFSET(<ref> ; 0 ; 0 ; <rows> ; <columns>) to build a range reference from a single cell reference. You may want to look into that.

Your INDIRECT(...) formula would then serve as the <ref> part of the OFFSET() suggestion above.

If you cannot find a way, attach a sample file to your question (edit your question and use the paperclip icon to upload a file), so we see what you are working with. Remember, this is a public place. Remove all confidential info from files before uploading.