Need formula for search and conditional formatting

General discussion on MS Excel and its spreadsheets.

Need formula for search and conditional formatting

Postby Gregory A. Chase » Tue May 06, 2014 5:10 am


I'm in need of some assistance for a project at work. We're trying to automate a report by conditionally formatting a cell or cells based on information that is updated daily on a separate worksheet in the workbook. Normally this wouldn't be a problem, but I need to match data in 2 columns, then grab the value of a third column in that matching row. Based on the value in that 3rd column, I want to color a cell in another sheet.

So, find a match in Col C, of which there will be many. Then in those rows that qualify, find a specific value in Col D. Once that row is identified (there can be only one row that matches both), go look at Col J and determine if it contains a 1 or 0. If it is a 1, apply a color to the cell on Sheet 2. Each cell in 'Sheet 2'!C3:AA4 (or more) should run this test on Sheet 1 to determine if it's cell should be colored or not.

I've attached a sample so you can see what I'm talking about.

Can anyone help with a formula that will make this happen?

There will be thousands of rows on Sheet 1 and it will be updated (pasted over) daily, so the formula should account for that size.

Thanks in advance!
Gregory A. Chase
Posts: 22
Joined: Mon Mar 24, 2014 6:14 am

Re: Need formula for search and conditional formatting

Postby MikeLON » Mon May 12, 2014 5:11 am


If you format your Sheet 2 cells as General, instead of Text, you could use:
=SUMPRODUCT(('Sheet 1'!$C$7:$C$15=$A$3)*('Sheet 1'!$B$7:$B$15=$B$3),'Sheet 1'!J$7:J$15)
This will return 0 if there are no matching 1s in column J, or a number greater than 0 otherwise (unless, perhaps, you have -ve numbers in column J).

Posts: 32
Joined: Tue Mar 25, 2014 6:18 am

Re: Need formula for search and conditional formatting

Postby FoxalTO » Mon May 12, 2014 5:15 am

Open sheet 2 - Select the range to format ( say C3:AA4)
Select the Home Tab - Cond Formatting - manage rules
Click New rule - Use a formula....
Select " New rule and enter
Code: Select all

Select which format you want and you're done

If you are not familiar with the SUMPRODUCT function read this link
Thank you,
Posts: 26
Joined: Tue Mar 25, 2014 6:25 am

Return to MS Excel- Help Forum

Who is online

Users browsing this forum: No registered users and 0 guests