Conditional Formatting based on multiple criteria

Question

Good Evening,

I have a sample data in the attached spreadsheet. I want to create an icon set conditional format based on when the employees start as well as if they are within the sales numbers. I created a table to showcase the criteria of those particular months and where the sales numbers should be (expected numbers). If they meet or exceed the numbers the icon should appear green. If the sales numbers fall between the two yellow columns, they should appear yellow. If numbers fall below the lowest yellow number, the sales data should appear red. I have included a sample of how I want it to appear AFTER the conditional format is applied. Please help!

Thank you,

Yvonne

Answer 1

You cannot do this in the Sales Number column itself but you can use a helper column.

The formula in D5 is

=SUM(--(C5>=VLOOKUP(B5,$A$16:$F$30,{2,4},0)))

If you don't have Office 2021 or Microsoft 365, confirm the formula with Ctrl+Shift+Enter.

Fill down.

This is what the rule looks like:

Answer 2

I was able to use vlookup it works only for one cell but it doesn’t allow me to copy the formula down and grab the different values. And since the data is large it will take a long time to create individualized formulas for each cell to match the month.

Answer 3

my apology. I forgot to add the spreadsheet.

https://www.dropbox.com/scl/fi/g13bji396lpj6iausa4j8/conditional-formatting-sample.xlsx?dl=0&rlkey=iivb2n43wjqqgvurc5loy2mg4

Answer 4

You can upload a sample workbook to a cloud service such as OneDrive, Google Drive or Dropbox.

Obtain a link to the uploaded file and post that link in a reply.