December 4, 2021
I am using an INDEX/MATCH formula as an array, and it works somewhat. When there is not a match, the result is "#N/A". And I want the result for any errors like this to display the result in the cell above it. Attached is a copy of my workbook, the Sections and Comments tab shows column C in blue, this is my array formula I am speaking of. For example, Row 10 of this document references Page 1, row 15. But if you go to the sections tab, you will see there is no corresponding record. The Sections tab lists the starting page and line for each section. So in this case, anything in between (which shows as a #N/A), should show as the Section above. So page 1, line 15 thru 1 line 16 (#N/A errors), should all show as "Document Overview". There is no change until the next section begins, which is on row 16 (page 2, line 1 of the document). And then this section should automatically fill rows 21-28 as well. I hope that makes sense. Thanks in advance.
Update: I just realized that this format (despite what I found in my Google Search) fid not require an array formula. So once I removed the array aspect from my formula, I just added a component for IFNA to handle my specific issue. problem solved!
or (without named ranges)
~~ so this thread can be closed out as solved