Filter function not detecting values

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 co...

excel

Karinanzr

Copper Contributor

Mar 08, 2024

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=104929125651435008341&rtpof=true&sd=true

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.

Patrick2788

Silver Contributor

Mar 08, 2024

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))

Forum Discussion

Filter function not detecting values

Resources