0

I have a formula =OFFSET($AL$5,12,0) that gives me the value I want from column AL. I want to make the "$AL$5" part dynamic, and looked up to match the same value in column F.

=(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5"

But when I try to combine the two formulas, Excel errors out: =OFFSET(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0))),12,0)*100

Does anyone know how to make this work? Thanks in advance!

  • I don’t understand what you’re trying to do.  It feels like you’re using an approach that unnecessarily complicated.  Please give an example of what data you have and what result you want. … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Feb 8 '19 at 0:36
2

If this =(CELL("address",INDEX(AL:AL,MATCH(F2,$AL:$AL,0)))) outputs "$AL$5", then the output is still text, not really a cell address. In order to convert it to a cell address, you need to wrap an Indirect() around it.

But since you're using Index/Match already, you don't need to convert the cell address into a text and then convert it back to a cell address.

Index returns a range and you can use that inside the offset.

Or, better yet: since you seem to offset 12 rows from the cell found by Index, you might as well build that into the Index statement directly. Add 12 rows to the number found by Match.

The final formula is

=INDEX(AL:AL,MATCH(F2,$AL:$AL,0)+12)*100

In words: In column AL, look for the value specified in F2, go down 12 rows and multiply that value by 100.

Let me know if that works for you.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.