Conditional Formatting with VLOOKUP formula

1
votes

When I enter this formula into a cell I get the desired boolean value:

=VLOOKUP(VALUE(MID(C5;1;FIND(" -";C5)));fb_accs;9)<>"-"

First I'm extracting the ID value from a cell mixed with text. And then checking if the 9th column for that row in particular in the fb_accs table in another sheet has no - in it.

However, when I copy that exact same formula and apply with conditional formatting to 1 column of the current sheet (range =$C$3:$C$10) it gives me an error saying:

There's a problem with this formula.

What can I do to solve this error?

1
excelexcel-formula
Shouldn't that be FIND(" -";C5)-1 ? – user4039065
The VALUE() gets rid of the trailing space so it does work – Tom Sharpe

1 Answers

2
votes

You have to use INDIRECT when referring to a table in a conditional format formula

=VLOOKUP(VALUE(MID(C5,1,FIND(" -",C5))),INDIRECT("fb_accs"),9)<>"-"

See this article