Spill and INDIRECT

Copper 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,

 

=IFNA(INDEX($M$3:$M$12,MATCH(U3#,$L$3:$L$12,0)),INDIRECT(ADDRESS(ROW(U3#)-1,COLUMN())))

 

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.

 

Mike

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()):
OFFSET(H16#,-1,0)

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

@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

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.

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.