What's wrong with this formula for conditional formatting

Hi all,

I've been breaking my head on this all day, can't figure out why it isn't working. All help is welcome.

I have a worksheet with a list of processes. To the right of the data I've added columns that look up issue numbers based on the process name in an issue table on another worksheet. I want to apply conditional formatting on the issue numbers to highlight their status. The status is contained in the same issue table.

To look up the issue numbers I use a helper column in the issue table and this formula:
IFNA(VLOOKUP($B4&" - "&COLUMNS($O$1:O1);Findings;MATCH(Findings[[#Headers];[Nr]];Findings[#Headers];0);FALSE);"")

Then in my conditional formatting I use the calculated issue number (the outcome of this formula) to lookup the status in the issue table Findings. So for the status closed I use the formula:
ISNUMBER(SEARCH("closed";VLOOKUP(O4;Findings[[#Data];[Nr]:[Status]];MATCH(Findings[[#Headers];[Status]];Findings[[#Headers];[Nr]:[Status]];0);FALSE)))

But when I put the formula in the conditional formatting dialog, nothing happens. I do see that Excel puts extra double qoutes (") around my formula.
I have tested the formula in a regular cell and only get TRUE or FALSE values, so that should not be a problem, right?

What am I missing here?

Thanks in advance.

Yes, I'm starting the formula with the equal sign, I just posted here without them

I figured Excel thinks it's a string, but I can't find out why....

This will go faster if you attach your file. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

So I've attached an anonimized version of the file. Thanks in advance

I am still looking into this but I believe that structured table references do not work in conditional formatting. Using INDIRECT is one workaround but very ugly in this case. I'll see what else I can find.

I changed the formula to use a regular range instead off the structured table reference and that indeed works. Thanks!

Still curious if you can find a more robust solution.

I used the structured table references to be more flexible in adding or removing columns from that table. With the current formula their is a risk that they break when I add or remove columns. So the robust solution for me is regaining that flexibility. Nonetheless, it works now.

rorya's suggestion is a very good one, although the only hitch in your formula would be the column count in VLOOKUP. Otherwise the formula should automatically reflect any addition or removal of columns. To manage the VLOOKUP count, you can use COLUMNS(a1:b1) where a is the first column in your VLOOKUP range, and b is the target column. That will be robust against changes. I can provide details if you show the formula you ended up with.

I'll give it a try. If not, I'll keep the formulas that are working now and be aware of the adding and deleting columns.

Thanks for the help.