How to locate a cell and then use it in a range?

[excelos]

Hello

Let's say we use an XLOOKUP to locate a particular cell which is e.g. B4.

How can I use this cell into a range e.g. B4:C10 for another formula?

And moreover, how do I use its column only or row only in a range for another formula, e.g. B:C or 4:10?

Last, how do I choose to use it as absolute or relative reference, e.g. $B$4:C10 ?

Thanks!

 

[arthurbr]

Have a look at the INDIRECT function

 

[excelos]

Yes, I plan to use INDIRECT/ADDRESS formulas, but does it work like that?

INDIRECT(ADDRESS(some formula)):C10

Also, what about getting the column or the row only from an ADDRESS?

 

[arthurbr]

Can't help with address, I never use it
OTOH ADDRESS needs column and row, so...
INDEX embedded in a formula returns cell references from which row or columns can be extracted, though.

 

[excelos]

INDEX embedded in a formula returns cell references from which row or columns can be extracted, though.

Thanks, how can I extract row or column from INDEX?

Also, =SUM(INDIRECT(H54):INDIRECT(I54)) does not seem to work!

 

[excelos]

Got it thanks!