Filter Formula and Match Formula - Not quite right

[Carl Stephens]

Hello All,

The below FILTER formula returns the exact desired result that I am looking for, with the first cell on the FILTER range being BP4, and when I run the MATCH formula below, that references BP4, this returns the exact result that I am looking for, however, when I drag the MATCH formula to BQ4, the result is "No", even though the correct data is in the FILTER range. Is this because the FILTER formula in only typed in one cell? Any ideas on why this is not working? Thank you.

=IFNA(TRANSPOSE(FILTER('CM-SE'!$B$6:$B$52,INDEX('CM-SE'!$D$6:$CX$52,,XMATCH($E4,'CM-SE'!$D$5:$CX$5))="X")),"")

=IF(ISNUMBER(MATCH(BP4, INDEX($B$4:$AZ$4,,), 0)),"Yes","No")

 

[StephenCrump]

INDEX($B$4:$AZ$4,,) is $B$4:$AZ$4, so your formula, when dragged one cell to the right, simplifies to

=IF(ISNUMBER(MATCH(BQ4, $B$4:$AZ$4,)),"Yes","No")

If it returns No, then the value in BQ4 is not found in B4:AZ4.

Two observations:

1. Your Filter formula points to worksheet 'CM-SE'. Your Match formula doesn't, but presumably should?
2. Is the lookup_array in the Match sufficiently large, ending at column AZ? Your Filter is looking at way more columns.