I need to extract a single value from a dynamic array in Excel. I only want the number highlighted orange from the "Value" column in my table named "FRAM_abundance_harvest".

The filter function below gets me the entire row of data, but I only want the value (356,034) to be returned.

`=FILTER(FRAM_abundance_harvest,((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark status]="Marked") *
(FRAM_abundance_harvest[Run type]="Late") *
(FRAM_abundance_harvest[Production type]="Hatchery")))`

CodePudding user response：

INDEX/MATCH vs SUMIFS in an Excel Table

The First Occurrence (INDEX/MATCH)

• This is an array formula (Ctrl,Shift Enter).

=IFERROR(INDEX(FRAM_abundance_harvest[Value],
MATCH(1,(FRAM_abundance_harvest[Source]="Ocean")
*(FRAM_abundance_harvest[Abundance_Harvest]="Abundance")
*(FRAM_abundance_harvest[Run_Type]="Late")
*(FRAM_abundance_harvest[Production_Type]="Hatchery")
*(FRAM_abundance_harvest[Mark_Status]="Marked"),0)),"")

The Sum (SUMIFS)

=SUMIFS(FRAM_abundance_harvest[Value],
FRAM_abundance_harvest[Source],"Ocean",
FRAM_abundance_harvest[Abundance_Harvest],"Abundance",
FRAM_abundance_harvest[Run_Type],"Late",
FRAM_abundance_harvest[Production_Type],"Hatchery",
FRAM_abundance_harvest[Mark_Status],"Marked")

• If no match is found, the first will return "" while the second will return 0.
• IMO, the SUMIFS formula seems more relevant.
• You could use five cells for the five columns and replace the hardcoded values with the cell references. Also, you could create dropdowns in the mentioned cells and easily change the result for different values of the five columns.

CodePudding user response：

Place the whole formula within an INDEX() Function, as shown in image below,

=INDEX(FILTER(FRAM_abundance_harvest,
((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark Status]="Marked") *
(FRAM_abundance_harvest[Run Type]="Late") *
(FRAM_abundance_harvest[Production Type]="Hatchery")),""),,6)

Or, Please note that O365 is required for the below function as well,

=DROP(FILTER(FRAM_abundance_harvest,
((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark Status]="Marked") *
(FRAM_abundance_harvest[Run Type]="Late") *
(FRAM_abundance_harvest[Production Type]="Hatchery")),""),,5)