Filter Table Function Excluding Columns from Another Table

Hello,

I have a FILTER function that tries to exclude matched records from a second table. Current formula.

Please Login or Register  to view this content.

The formula above works if there is only 1 record in the lookup table (tblNonEmpl above) but if I add more than one record, the formula returns an #NA error.

Thoughts on how I can solve this?

It's because when it compares an array with a single value, it compares each value in the array with the single value.

Similarly when it compares two arrays with the same dimensions, it compares each corresponding pair (e.g. 1st value with 1st value, 2nd with 2nd)

However when you try to compare 2 arrays of different dimensions, it doesn't know what to do.

Try replacing this:

NOT(tblCerts[Full Name]=tblNonEmpl[Resource Name])

with this:

NOT(ISNUMBER(MATCH(tblCerts[Full Name],tblNonEmpl[Resource Name],0)))

Money! Thank you sir, that worked! Thanks for the explanation as well.

You're welcome, glad it helped