Spill and INDIRECT

New Contributor

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 in a fixed array).  If it's there enter it into the cell, but if it isn't there then enter the value from the cell above the current cell.  I have entered the following formula,




It works for the first part and finds the values ok but I just get #VALUE as soon as it can't find the data.  If I take out the ADDRESS part of the equation and try it on its own then I get the cell address of the spill array set back by one row, so that seems to be working.  The problem comes in when I add in the INDIRECT function.  Does anyone know why and if there's a workaround?


Thanks in advance.



6 Replies
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. ?

Why don't you just use OFFSET instead of INDIRECT(ADDRESS()):

oh and as to why, I'm pretty sure INDIRECT() doesn't work with array input



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,



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:


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:



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

It's not clear what the goal is with the formula. Is this part of a larger project?
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.