Conditional formatting if cell is blank, matching two criteria with multiple possible instances

[IsobelSandbach]

I'm setting up a spreadsheet to track costs against projects. Each individual order is listed in the Cost Data tab where it is assigned to a category and the cost listed. In the Costs & Profit tab these costs are then totalled in relation to 3 categories:

Material Costs Supply & Fit = "Subcontractor"
Material Costs Supply = "Material", "Plant Hire" or "Transport/Courier"

Labour Costs = "Labour"

Cost Data

Costs & Profit

I want to apply conditional formatting to highlight cells K, L, M in Costs & Profit when costs are incomplete, i.e. an order exists in the Cost Data tab so costs are expected, but cost has not yet been input. In the example above, I want K484 to highlight yellow, as the Cost Data tab shows we are expecting further sub-contractor costs for order 2. M484 does not need to be highlighted as there are no orders for Labour, so we are not expecting any costs here. 

I've tried using this formula:
=AND(VLOOKUP(A484,'Cost Data'!A:H,4,FALSE)="Sub-Contractor ",VLOOKUP(A484,'Cost Data'!A:H,5,FALSE)="") 

This works for single orders per category, however as it's a VLOOKUP it only finds the first matching instance. I need a way to make this work when there are multiple orders that match job no. and category, if cell E is blank for any.

[#2 · Accepted solution]

Hi @IsobelSandbach 

Thanks for the clear problem description (a sample workbook would have saved time...). Assuming I didn't make mistake in your mapping:

No idea how large (#rows) is your Cost & Profit table. If thousands of rows this might impact the perf. of your solution workbook...

[#3]

@L z. Thank you, that works!

[#4]

@IsobelSandbach Glad I could help + Thanks for providing feedback and marking as solution (helps those who Search)

Nice EOD...