Nov 04 2022 09:06 AM
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
Nov 04 2022 09:21 AM
Nov 04 2022 10:53 AM - edited Nov 04 2022 10:55 AM
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
Nov 10 2022 01:59 AM
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
Nov 10 2022 04:20 AM - edited Nov 10 2022 04:26 AM
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.
Nov 10 2022 05:40 AM
Nov 11 2022 02:35 AM