Array formula using OFFSET, INDEX, INDIRECT & MATCH returns #N/A error (otherwise OK)

Greetings to all,

I am a novice. Both in Excel and internet forums. I can usually find the answer I need by scouring such forums as these, but right now I'm stumped. I'm hoping some bright spark who reads this can provide me with a simple solution.

On Sheet1 I have multiple columns of data (all text). On Sheet 2 I wish to extract particular segments of any given column.

I am achieving the results I want with the following formula, however it is also returning #N/A errors which I am trying (unsuccessfully) to hide or otherwise eliminate.

This is my formula:
{=OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1)}

I have tried nesting the whole thing in IFERROR(, IF(ISERROR, IF(ISNA & IF(ROWS(I$5:I5>$I$4)etc. - ($I$4 being the cell containing the row count for the data I am extracting). Frustratingly, they all return the same result - including the #N/A errors when all sought rows have been retrieved, but the formula keeps going.

Being a novice, I'm not sure how much information is too much or too little.

Below are the full formulas I have tried, but have failed.


{=IFERROR(OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1),"")}

{=IF(ISERROR(OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1)),"",OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1))}

=IF(ISNA(OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1)),"",OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1))}

{=IF(ROWS(I$5:I5)>$I$4,"",(OFFSET(INDEX(INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),MATCH(E11,INDIRECT("Movie_Details!"&"R"&$D$8&"C"&$D$5&":"&"R"&$D$9&"C"&$D$5,FALSE),0)),,,D12-E12,1)))}


I hope I have made my problem clear enough to tackle.

Thanks in advance,
Melpa

P.S. I have attempted to attach the workbook.

Hello and welcome to the forum.

I would urge you to rethink your data layout. You are making any analysis far too complicated.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database with the following three column labels

Name of Movie
Information Type ' * rows here will consist of one of the 13 or so item of text you currently have in bold on the Movie Details sheet.
Description

So it would look like the following

Movies.JPG


The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

Hi,

The following macro will create a three column database for you based on your Movie Details sheet. See also attached sheet.
The new data sheet is VBA code name 'Sheet3' which is referenced in the macro.

Please Login or Register  to view this content.

My pleasure and thanks for the rep.