Forum Discussion

MikeB365's avatar
MikeB365
Copper Contributor
Nov 04, 2022

Spill and INDIRECT

Can anyone help me...??   I have a dynamic array which is derived from a SPILL.  What I want to do now is an INDEX(MATCH) on that data (look at the current row value in the SPILL array and find it ...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 04, 2022
It works for me in the sample I created. The formula's arrangement is a bit unusual. Do you happen to have a sample workbook you can share or link to via OneDrive/Dropbox, etc. ?
MikeB365's avatar
MikeB365
Copper Contributor
Nov 10, 2022

Patrick2788 

 

Thank you very much for your reply, and apologies for taking my time in responding to you - I'd set it to email me when someone replied but for some reason it didn't.

 

Anyway, I have attached an example sheet showing what I get when I enter the formula as I think it should be.  I have also added in another column trying to do the same job but using OFFSET instead of INDIRECT as mtarler suggested.  Unfortunately neither were successful.

 

Thanks again,

 

Mike

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 10, 2022
    It's not clear what the goal is with the formula. Is this part of a larger project?
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 10, 2022

    OFFSET didn't work because you referenced itself H2# instead of E2#. I don't understand what you are trying to do, but here is the formula "as it is" and maybe we can talk about what you really want it to do:
    =IFNA(INDEX($C$2:$C$11,MATCH(E2#,$B$2:$B$11,0)),OFFSET(E2#,-1,0))

     

    EDIT: in looking at your formula and the numbers I'm wondering if what you were trying or hoping to do is a lookup that 'holds' the previous value until it finds the next value.  That is built into the LOOKUP() functions like:

    =VLOOKUP(E2#,B2:C11,2)

     

    but again we can help better if you explain what you really want/need.

    • MikeB365's avatar
      MikeB365
      Copper Contributor
      Nov 11, 2022
      Apologies, I thought I'd been pretty thorough in my explanation but it just goes to show! However, your last suggestion of the VLOOKUP has worked and done as I wanted, so thank you very much for your help, it's much appreciated.