Returning Cell Address based on a Lookup Function

[ChristiaanV]

Hi,

I want to use an offset formula to select a range of cells that I will use as a series in a chart. However, not only does the number of cells in the range vary (hence, the offset formul), but the location of the reference cell is not fixed; it also varies by date.

What I'm looking for is a way to specify the address of that reference cell. I tried using the CELL function combined with a VLOOKUP function:

=CELL("address",VLOOKUP(date,table,1,false))

But if I try that I get the message, "The formula you typed contains an error."

How could I go about doing this?

Thanks,
Christiaan

[NBVC]

Try:

=CELL("ADDRESS",INDEX(table,MATCH(date,table,0)))

assuming table is a 1-column range....

[ChristiaanV]

That did the trick. Thanks NBVC!

[ChristiaanV]

I may have spoken too soon. The INDEX MATCH bit allows me to specify the reference cell address, and I made a named formula out of it.

But when I plug the named formula as the reference value into my OFFSET formula, I get a #VALUE! error result. I'm guesing the problem is that the CELL function identifies the address as a text value, but I need to plug it into my OFFSET formula as a cell reference, not text. How can I tranform the address from text to a bona fide cell reference?

Thanks in advance for the help,

Christiaan

[kraljb]

Use of the INDIRECT formula would be needed to take a Text Cell Reference and make it into a Cell Reference

Hope that helps..
John

[NBVC]

I may have spoken too soon. The INDEX MATCH bit allows me to specify the reference cell address, and I made a named formula out of it.

But when I plug the named formula as the reference value into my OFFSET formula, I get a #VALUE! error result. I'm guesing the problem is that the CELL function identifies the address as a text value, but I need to plug it into my OFFSET formula as a cell reference, not text. How can I tranform the address from text to a bona fide cell reference?

Thanks in advance for the help,

Christiaan

As mention by John, you need to incorporate the Indirect Function....

e.g. =OFFSET(INDIRECT(AddressFormula),1,2)

where AddressFormula is the name you gave the formula.

[mikerickson]

Another approach would be to use a CHOOSE for the reference cell argument, as in

= OFFSET (CHOOSE(A1,$B$1,$B$2,$B$3,$B$4),0,0,1,1) will depend on the value in A1 (and error if A1>4 or 0>A1).

[ChristiaanV]

Thanks for the help, but I can't seem to get it. If I try to use a named formula as the series formul, I get an error message regarding the format. And if I try to enter an OFFSET formula as a series formula, Excel doesn't permit me - I believe it highlights OFFSET as an invalid function.

[all4excel]

If i am not mistaken u want to get the Address of the cell using the Vlookup or Index function and then set it as a "reference" in the Offset function..

you want to have a Dynamic reference in the offset function..

I would like to help you ,can u please attach the file...showing dummy data..

I have used a lot of INDIRECT function of late...

So i can try ...