CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Dontacceptcookies
Dontacceptcookies

Reputation: 1

On Excel, how do I highlight a cell if the value inputted is not in my reference table?

I need help with conditional formatting on Excel.

I have two sheets, on Sheet1 I have 2 columns : Demand Planning Cluster and Region. On this sheet people are gonna input both DPC and Region. Each Demand Planning Cluster has between 1 and 5 regions that are applicable. There are only 5 values to Region and around a 100 DPC. This is mapped in a table on Sheet2.

I want to highlight the region cell if the region value inputted is not linked to the Demand Planning Cluster on Sheet2.

I've written this formula and used it with conditional formatting rule :

=IF(ISNA(MATCH(C3, INDEX(sheet2!L63:P63, MATCH(B3, sheet2!$K$63:$K$156, 0),), 0)), TRUE, FALSE)

My region values are on columns L to P, and my DPC are on the column K.

The formula doesn't work for some reason, but I was also wondering how do I apply this rule to all the lines in the region column?

My reference table looks like this

Upvotes: 0

Views: 47

Answers (1)

Black cat
Black cat

Reputation: 6246

You have to define the whole range of Region, and on @BigBen comments use absolute references.

=IF(ISNA(MATCH(C3, INDEX(sheet2!$L$63:$P$156, MATCH(B3, sheet2!$K$63:$K$156, 0),), 0)), TRUE, FALSE)

Upvotes: 0

Related Questions