Why does this filter function return the value error?-CodePudding

In the formula below, a #Value! error is returned.

=FILTER(FIND(";"&AS4756:AS4762&{6,7},AG4756),ISNUMBER(FIND(";"&AS4756:AS4762&{6,7},AG4756)))

Where ag4756 is 8034000;Y8033343543543;Y;38918;BS7 9XL;9;Male;N;N;N

and AS4756:AS4762 is

BS
NP
SN
GL
BA
CF
TA

When I chose evaluate formula, it returns the number 31 for the string BS7, and errors for all others. I'm trying to filter out the errors from the result, so I only get the result which is not an error. It also correctly returns TRUE for BS7, and false for all the others. But then the next step results in the error. Can anyone help?

edit: I want to return the position of the substring that starts with BS7. Or BS6, or NP6, or NP7, and so on.

CodePudding user response：

Alright, I hope the following helps you out a bit; let's assume data (vertical) {BS;NP;SN;GL;BA;CF;TA} to be in A1:A7 and the lookup-string to be in B1. You are then concatenating a 2nd (horizontal) array {6,7} which would make up the following matrix:

Now to find these values in B1 using FIND(";"&A1:A7&{6,7},C1) would result in:

The next step for you would be to gather the numeric value. However, for the 2nd parameter, you have provided a 7*2 matrix and not a 1D-vertical or horizontal array of values. Therefor FILTER() will error out on exactly that step, returning the '#VALUE!'! And even so, if you would try to use MMULT() tricks to retrieve this array correctly you'd still need to deal with the fact you are trying to filter a matrix from the 1st parameter that is filled with errors. Therefor, I've shown how you could get the position from a relative easy formula that would deal with errors too:

=MAX(IFERROR(FIND(";"&A1:A7&{6,7},B1),""))

And to get the correct substring, use:

=MID(B1,MAX(IFERROR(FIND(";"&A1:A7&{6,7},B1) 1,"")),3)

Now that leaves us with the probability you have more potential substrings you'd like to be returned. If that is the case, let me know in the comments.