Filter function not detecting values

[Karinanzr]

Hi! Never posted here but hopefully I can get a quick response. I’m building a payroll calculator for a client and have hit a snag. My problem is as follows;

I have an array that includes some columns with values that come from a =textafter formula. I also have columns with values that just reference another sheet/cell with text in it. So in the array the reference cells have something like =sheet name (cell) 

the filter function seems to pick up the values from the simple cell reference formulas fine. But it won’t detect the values in the column with =TEXTAFTER formulas. 

is this a limitation of the function? Is it unable to detect values in columns with formulas in them? 

please advise. Thank you!!! 

[#2]

@Karinanzr 

FILTER looks at the values of cells, not at their formulas, so it should work, in principle.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Absolutely, thanks so much. Can't figure out how to add an attachment so I uploaded it to google drive. Hopefully that works, it says unknown function when viewing it in a browser but seems to download normally.

https://docs.google.com/spreadsheets/d/1kgmqbz4tbGxtQsM5nF_alOyuTCSVL6Fx/edit?usp=sharing&ouid=10492...

So the tab "clean data" just references cells in the tab "raw data" with the addition of column L, among others. 

If you see on the last tab the "master sheet", cell D11 contains a working formula. However cell E11, where I try to reference column L in the "clean data" tab, returns the #N/A error, which is incorrect. There should actually be three results. I just put the two formulas side by side for comparison purposes, and have not added the IFERROR and ROW portions yet. 

So the problem is column L which contains (as an example) the formula =TEXTBEFORE(TEXTAFTER(K4,"Couples with ",,1,,),",",,,1) in the "clean data sheet". These values are not being detected. 

Hopefully this makes sense. I'm sure there are easier ways of doing what I'm trying to do here, but I've only ever gotten through Excel projects by googling, so, forgive any redundancies. 

[#4]

@Karinanzr 

At first glance, there's some issues with the data. It's not entirely clean.

You're not getting FILTER results because the errors need to be checked in L in the Cleaned Data sheet.

Clear out the formulas in L and try this:

=LET(name, TEXTBEFORE(TEXTAFTER(K2:K200, "Couples with ", , 1, , ""), ",", , , 1, ""), TRIM(name))

[#5]

@Karinanzr 

Thanks. The problem appears to be that most of the formulas in column L of the Cleaned Data sheet return #N/A. You can suppress that by supplying the if_not_found argument of TEXTBEFORE and TEXTAFTER:

=TEXTBEFORE(TEXTAFTER(K2,"Couples with ",,1,,""),",",,,1,"")

The formula in E11 on the Master Pay Sheet then returns a spilled range:

See the attached version.