Results 1 to 3 of 3

Thread: Problem with Look Up Array Formula

  1. #1
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010

    Problem with Look Up Array Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi
    Ive been looking at a problem and Ive encountered something I don't understand. The formulae Im using on the attachment (Sheet 3) are to pull records from Sheet2 based on a Payment method of Cash or Cheque using FIND( to verify the entries inCol B of sheet2 (the "METH" range). To get more flexibility I created a UDF to check the payment methods (Fstr). Im using this in one cell (B5) on Sheet3. The others on Sheet 3 use the FIND function. The return value in B5 (Sheet3 is
    wrong because there is a difference in the way the array filtering operates with the two functions and yet the data being passed looks exactly the same.
    THere are two arrays of 30 elements
    METH Array
    TRUE = Cell Value either "Cash" or "Cheque"
    FALSE = Cell Value not as above

    Row Numbers Array {1 to 30} From ROW(METH)-4

    An IF statement returns row no if TRUE and FALSE if FALSE.

    Now - With the FIND function the matching is one for one between the two arrays, returning 30 elements, but line 1 is FALSE and so the SMALL function returns 2 ("Cheque") which is correct.
    With the Fstr UDF it seems to be matching every line with every element in the METH Array, so 1 is returned which is wrong. Bearing in mind that the data appears identical in Evaluate formula
    I cant understand whats causing this and how to correct it.
    Could it be that Im only returning the values tested by the UDF and not the range objects ?
    I know I can just leave the FIND in there, but the UDF will provide more functionality, and I would like to understand this. Id appreciate any help.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Also posted here - will advise when sorted
    Thanks.

    http://www.mrexcel.com/forum/excel-q...ml#post3636499

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Hercules1946 View Post
    Also posted here - will advise when sorted
    Thanks.
    This problem has now been resolved - Thanks
    Last edited by Hercules1946; 2013-11-18 at 04:46 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •