Excel Offset with match / Address / Cell

Question

Hi,
if I want to use offset but do not know the row / column number how can I retrieve the

cell reference for my starting point in offiset,

I have tried the following ;

CELL("address",INDEX($A$1:$A$13,MATCH(E3,$A$1:$A$13,0),1) ) = $A$4

But when I use this in offset I get an error, so I'm doing something wrong or I have to try a different approach .

Richard

Answer 1

Hi Richard

Could you please, give us more details of your scenario, and what you want to achieve?

What's the formula for?

Regards

Jeovany

Answer 2

After some thoughts

The CELL() function returns a cell address as a Text value, hence the error. To convert it to a reference we need to wrap it with the INDIRECT() function.

Following the details in your picture, here is a working formula

=OFFSET(INDIRECT(CELL("address",INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1))),1,1,1,1)

Note:

If your goal is to return the value one row below, one column next to the matching criteria (cell B5 in the picture above) then you might consider NOT using volatile functions like OFFSET(), CELL(), and INDIRECT()

Here is an alternative formula for equal result =INDEX($B$1:$B$13,MATCH(D1,$A$1:$A$13,0)+1)

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany