Offset with indirect and MATCH

[S_Asks]

Hi There,

I'm trying to use Indirect with Offset. This way trying to match 2 different values.

The formula I am using is as follows:
=OFFSET(INDIRECT("'"&"Analyser list"&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&"Analyser list"&"'!"&"A1"):INDIRECT("'"&"Analyser list"&"'!"&"D1"))-1,COUNTA(OFFSET(INDIRECT("'"&"Analyser list"&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&"Analyser list"&"'!"&"A1"):INDIRECT("'"&"Analyser list"&"'!"&"D1"),0)-1,20,1)),1)

unfortunately this results in the following error

I understand the error but I don't know how I can make the formula work.

Someone knows how to get this to work? I think the problem is with the Indirect part, but no Idea how to fix it

thanks for the help

 

[S_Asks]

I got it to work! Thank you very much for the help Jason!

 

[jasonb75]

You're welcome! Glad you got it working in the end

Out of curiosity, what did you do to fix it? I can't see it in the screen capture that you posted but usually the double quotes mean a typo in the formula. The one in my post was correct but they break so easily in those boxes. Often the cause is as simple as hitting one of the cursor keys when you enter the formula, or pasting it twice without realising.

I'll post another one for you later tonight using the newer functions in 365. The existing method should work fine with any version of excel so no need to change it, but you may find the 365 method easier to set up and understand if you have to do this again at any time.

 

[S_Asks]

The problem was in the quotations, I copied the code from you again and the problem was solved (So maybe bad copying not sure.
Thank you, looking forward to the 365 version!