Problem with array formula and user defined function

[Hercules1946]

I have an array formula thats pulling the lines from sheet A to sheet B based on a criteria column in A If the criteria says "Cash" or "Cheque" that data is pulled onto B.
Excel builds two arrays one showing the contents of the A criteria column and the other a set of TRUE and FALSE indicating (correctly) whether the criteria is met.
SMALL then works with the row numbers to find the lowest row number for the TRUE criteria routed by IF(TRUE)

(a) Both the row numbers array and the (TRUE/FALSE) array (30 elements in each) are correctly listed and lined up with each other by Evaluate Formula. Now I can state my problem.

If Im using FIND( to do the criteria test, then the correct row is returned. If I use my UDF it isn't. Everything is the same at point (a) with both functions.
The FIND route matches the two arrays on a one for one basis giving me 30 elements returning the line numbers (TRUE) and returning FALSE (FOR FALSE). (SMALL returns the correct row).
Th UDF route matches all the TRUE/FALSE with every entry in the row numbers so that SMALL picks 1 which is always available along with all the others

For info, my UDF uses Instr to check that the tested value is in the correct section of the (lookup) string of values (eg having a startpoint at position before <=5, as in "CashCheque....." ).

Why are the arrays handled differently by the two functions ? How can I get the UDF to work like FIND? Ive tried everything I can think of on this with no luck.

Any help would be appreciated.

This is also posted at:
Problem with Look Up Array Formula

will advise when resolved

 

[Hercules1946]

Did you try my variation of the UDF?
TRANSPOSE (either in the UDF or in the formula) shifts the problem to the case where you use Fstr on a A1:Z1.

Hi Mike
Yes, I changed the last line of the UDF to transpose the array. That fixed the problem.
Thanks very much, and apologies for not responding, Ive been completing the exercise now that its resolved

Hercules.

 

[Hercules1946]

Did you try my variation of the UDF?

Hi Mike
Yes, I will be incorporating your changes into the function coding. As you say it improves
the functionality taking into account passing a row, column (or both) cell structures.
Thanks for taking the time on this.

Hercules