Extract an Excel cell value from dynamic array

user_3167283 0
Viewed 243

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".

enter image description here

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")))`
excel-formula excel
2 Answers

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.

enter image description here

user_98140690

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

FORMULA_SOLUTION

=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() Function is still in the Insider's BETA-channels.

USING_DROP

=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)
user_81625200
Related