excelexcel-formula

COUNTIF() with output list of INDEX()

I have two dynamic arrays which I want to get the common values from. One of the arrays is being built with a INDEX() which gives me the following data quite nicely:

enter image description here

When wrapping this in a COUNTIF I get presented with an array with errors:

enter image description here

How would I go about doing this? My resulting formula looks like this:

=COUNTIF(INDEX('DATAMODEL - Prod spec attr'!A:A, FILTER(ROW(INDEX('DATAMODEL - Prod spec attr'!$G$3:$JE$405,0,ROW())), INDEX('DATAMODEL - Prod spec attr'!$G$3:$JE$405,0,ROW())=TRUE)), 'CS - Prod Spec Attr'!G:G)

The end goal is wrapping this all up in a FILTER() to get only the common values in both arrays.

Solution

  • I think the main issue you are experiencing is that COUNTIF() does not take an array where a range is expected.

    Try something along these lines:

    =LET(_arr1,<Functions_to_get_arr1>,TOCOL(IFS(XMATCH(_arr1,<Functions_to_get_arr2>),_arr1),3))