Excel stopped accepting array formula

[bradams]

I'm not entirely sure how to explain this issue, so apologies if the title is confusing or misleading.

I have a spreadsheet with a fairly large number of array formulas. Maybe there is a more efficient way of doing it but this is where I'm at for the moment. The formulas were working. However I needed to make a pretty minor tweak. Now when I hit Ctrl + Shift + Enter to enter the array formula, it just sits there. The only thing displayed in the cell is part of the formula, similar to what it would show if it were just text instead of a formula. I cannot get it to recognize the formula and return results. I've tried Refresh, Refresh All, saving and re-opening the spreadsheet, deleting and re-entering the formula, and anything else I could think of. I don't know if the problem is the overall size/complexity of the spreadsheet (it is one of the larger ones I've done) or if something else is going on. Thoughts?

If it helps, here is the formula:


=IF($A2<>"",IF(INDEX('Children Demographics'!B1:B2001,MATCH(1,($B2='Children Demographics'!$A1:$A2001)*($C2='Children Demographics'!$B1:$B2001),0))=0,"Unknown",INDEX('Children Demographics'!B1:B2001,MATCH(1,($B2='Children Demographics'!$A1:$A2001)*($C2='Children Demographics'!$B1:$B2001),0))),"")

1. The number of children will change over time. Currently there are around 1,500 so I've designed the spreadsheet to handle up to 2000.
2. I need to do a lookup against both the child name and a case number (a case can have multiple children but also need to guard against children with the same name in different cases).
3. In this example I am returning the child's date of birth that is in Column B of the Children Demographics sheet. If DOB is not listed, I need it to return "Unknown."

Thanks in advance for any help you can provide. I've been trying to figure this out for a while.

 

[Marcelo Branco]

Just an idea
To prevent Excel from having to calculate the formula twice you can use this custom format
[= 0] "Unknown"

Then use
=IF($A2<>"",INDEX('Children Demographics'!B1:B2001,MATCH(1,($B2='Children Demographics'!$A1:$A2001)*($C2='Children Demographics'!$B1:$B2001),0)),"")

M.

 

[bradams]

Just an idea
To prevent Excel from having to calculate the formula twice you can use this custom format
[= 0] "Unknown"

Then use
=IF($A2<>"",INDEX('Children Demographics'!B1:B2001,MATCH(1,($B2='Children Demographics'!$A1:$A2001)*($C2='Children Demographics'!$B1:$B2001),0)),"")

M.

Thanks but the reason I'm doing it twice is to return "Unknown" if DOB isn't entered (value of 0) and to return the DOB if it is a non-zero value.

 

[Marcelo Branco]

Thanks but the reason I'm doing it twice is to return "Unknown" if DOB isn't entered (value of 0) and to return the DOB if it is a non-zero value.

I understand, but what I meant was that this is not necessary if the custom format proposed is used. I suggested this because it looks like your spreadsheet has serious performance issues.

M.

 

[bradams]

I understand, but what I meant was that this is not necessary if the custom format proposed is used. I suggested this because it looks like your spreadsheet has serious performance issues.

M.

Sorry, I missed the custom format in your original post. I need to read more carefully.

 

[bradams]

Also, that was an excellent suggestion. It didn't solve the problem but it is a much smarter approach than what I was doing.

 

[RoryA]

Does this problem apply to all sheets or just one? Can you enter any formula into the cell that works? (I assume you've changed to use the custom format and formula that Marcelo suggested, so it can't be that your cell was formatted as Text before) Do you perhaps have the sheet set to display formulas rather than their results?

 

[bradams]

Well, I'll be. I must have accidentally changed it to show formulas rather than results. I have no idea how I did that. Man, that was a costly (in terms of time) mistake. Thank you!