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