I am trying to find/match the sorted data F4:F7 (rows limited here) with the dataset B4:B13 and then print the matched data as in H4:H7.

I am using the following formula in H4 which works well, when the sorted data array is defined for the existing text fields F4:F7.

=FILTER($B$4:$B$13;MMULT(--ISNUMBER(FIND(TRANSPOSE($F$4:$F$7);$B$4:$B$13));SEQUENCE(COUNTA($F$4:$F$7);;;0));"")

But when I change the sorted data list from F4:F7 to F4:F10 it is giving #VALUE! error. Actually I don't want to limit the sorted data list rows, because in my case it is dynamic. How should I get rid of this error?

CodePudding user response：

To have matrices multiply, the columns of the first matrix must equal the rows of the second matrix. That being said, your formula seems way too complicated for what you have shown you want to do.

If you want to reference the names for some reason, just do =VLOOKUP(F4, $B$4:$B$13, 1, 0) and drag down.

Another way is something like =IFNA(INDEX($B$4:$B$13, MATCH($F$4:$F$7,$B$4:$B$13,0)),"")