New Contributor

# 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 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?

Mike

6 Replies

# Re: Spill and INDIRECT

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

# Re: Spill and INDIRECT

OFFSET(H16#,-1,0)

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

# Re: Spill and INDIRECT

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

# Re: Spill and INDIRECT

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.

# Re: Spill and INDIRECT

It's not clear what the goal is with the formula. Is this part of a larger project?

# Re: Spill and INDIRECT

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.