Multiple Criteria in a Filter command

[garyj]

Hello...

So I have dealt with multiple criteria before, but this includes checking something on another table, and the regular method doesn't seem to work. So I did some research and found a Filter with Match that seems to make sense... or else I was deluded... haha.

I am attempting to filter all rows in Table1 (top left) with a match statement that looks for 3 criteria to be true. If all are true then the multiplier should evaluate the items to 1, which is the requirement for the match formula shown. The dynamic formula spills below the yellow cell showing all the columns - more than the desired result. It should only show the items in the light orange background. I separated the three criteria, placing them into columns K, L, and M respectively.


Column K does the xlookup to check if the same IDNo in Table2 (top right) has been 'Pd in Full'. If not it returns True.
Column L checks to see if Table1[Budget Area] is "Accounts Payable", returning True if it is.
Column M checks to see if Table1[Credit] is greater than zero.
Column N spills the formula that multiplies the other 3 columns. There are only two rows that have 3 Trues, and evaluate as a result to 1.
But why doesn't the include part of the Filter command then exclude the zero amounts in Column J?

Anyway, it is easier to understand the problem from the file. How do I limit the records only to those evaluating to 1?
Here is the file.
Book1.xlsx

Gary

[Aflatoon]

Your MATCH function is causing the problem, and there is no need to use it at all. This should suffice:

=FILTER(Table1[Credit],(XLOOKUP(Table1[IDNo],Table2[IDNo],Table2[Pd in Full],FALSE)="")*(Table1[Budget Area]="Accounts Payable")*(Table1[Credit]>0),"none")

[garyj]

Your MATCH function is causing the problem, and there is no need to use it at all. This should suffice:

=FILTER(Table1[Credit],(XLOOKUP(Table1[IDNo],Table2[IDNo],Table2[Pd in Full],FALSE)="")*(Table1[Budget Area]="Accounts Payable")*(Table1[Credit]>0),"none")

Hmm, not sure why I put it in there any more. Good solution, and thanks, Aflatoon.

I normally try to simplify any problems so that I can post the basic question. I then modify the answer to fit my need and go from there. So when looking at the attached file (which is the result), you will see your answer in the green cell. It works slick. Now this is where I encounter the next problem - see yellow cell. I found a solution which works using HSTACK. But it is clumsy and lengthy. I couldn't seem to find a way to accept the whole table, and so I added each column as needed. Is there a simpler solution?

Thanks in advance.
Gary

[Aflatoon]

I'll have to look at that later - my browser on this PC won't let me download anything from insecure sites like this one.

[barnie]

In Chrome it is easy to disable: Settings --> Privacy and Security --> Site Settings --> Insecure content --> You can either add specific sites to the Allow list by clicking Add next to Allowed to show insecure content, or adjust settings for general content.

[Aflatoon]

You could use something like:

=DROP(REDUCE("",CHOOSECOLS(I11#,1),LAMBDA(s,c,VSTACK(s,DROP(FILTER(Table2,T able2[IDNo]=c),,1)))),1)

[garyj]

You could use something like:

=DROP(REDUCE("",CHOOSECOLS(I11#,1),LAMBDA(s,c,VSTACK(s,DROP(FILTER(Table2,T able2[IDNo]=c),,1)))),1)

Amazing how well that works. I did a quick test, but haven't tested on all possibilities yet as I am on another mission which interrupted this one. (Ah, the programmer life!)
I looked each of the commands which are quite new to me: DROP, REDUCE, LAMBDA - and am still scratching my head trying to understand how it works. Gonna wear a hole through the scratching before I come up with an answer haha.

Thanks so much. I am going to mark this solved trusting that it will pass the tests in my particular application.

Gary