Conditional Formatting - based on a cell value compared to values in a table

Hi everyone,

I'm looking to write conditional formatting rules for cells that look at another cell's value in comparison to many values in a table titled TableFormatting. If the referenced cell equals a value in one of the table's columns one of eight different formats should apply to that cell. I've attached a simplified version of the workbook that helps illustrate my situation.

I've tried several variations of rules for the conditional formatting with no luck as of yet so I'm wondering if someone has an elegant solution.

Thanks so much and let me know if anything I've explained requires further clarification.

Sorry for the confusion.

I need the cells in column B (like B14) to have formatting dependent on cells in column ! (like A14). The formatting should be determined by comparison of the value in column A to the table TableFormatting. If the value in column A (like A14) is in the table column with the heading [General] then column B's cell (like B14) will get a general formatting. If the value in column A (like A14) is in the table column with the heading [Date] then column B's cell (like B14) will get a date formatting. etc... for comparison to the other table headings.

I was trying some rules using VLOOKUP but hadn't found something that worked.

Hopefully whatever rule works can use $A14 and $B14 so that the same conditional formatting rule can be applied to other cells in Column B (like B18 and B22) that will need the same type of conditional formatting.

Thanks and I hope this is more clear.

With some experimentation I've gotten the following formula to correctly return TRUE or FALSE in evaluating cell A14

=SUMPRODUCT(--($A18=FormattingTable[[#All],[$'#,'#]]))>0

However I cannot get this formula to be accepted in the conditional formatting window. If it's correctly returning TRUE or FALSE as a regular formula shouldn't it be accepted as a conditional formatting rule or am I missing something?