One thing to note is that if you are writing your formula directly into the conditional formatting rule, if you select cell A3 instead of actually typing it, Excel is going to make it an absolute reference ($A$3) by default, which is not what we want. Any value other than 0 equates to TRUE in the Conditional Formatting window, while a 0 is the same as returning FALSE. When written in the cell, this function returns a value of 1 or 0 depending on if it found those five digits at the beginning of the entry. When we close our parenthesis, our complete formula reads: To fill out the arguments for the LEFT function, you choose the first cell in the list (A3) as the text argument, and the number 5 for num_chars argument. It looks at the leftmost characters up to the number of places you determine. To do that we will use the formula called LEFT. But we want to make those values absolute ($F$3:$F$5) since they won't be changing.įor the criteria, we want to look at the first five characters of the product number. In our case it is our list of 5-character codes that the part numbers should start with.

The first is the range, and the second is the criteria.įor range, the formula is looking for the set of data that it should look through to find the criteria. There are many different lookup formulas and ways to go about this, but we're going to use the COUNTIF function. I prefer to write my formulas in a cell and then paste it in, but it's entirely up to you. That will open up a field where you can type or paste a formula. Then select New Rule.įor the Rule Type, choose the option that says Use a formula to determine which cells to format. You can access this by going to the Home tab on the Ribbon and selecting the Conditional Formatting drop-down menu. To start, we're going to open up the Conditional Formatting window. In my example today, I want to color cells for any Part Numbers that begin with a particular list of characters. You can use a formula to search, find, and apply formatting based on a partial match against a list of data. Let's say you have a list of items and you want to highlight the ones that start with a certain character or series of characters.

Conditional-Formatting-Partial-Match-List-of-Values.xlsx Download Conditional Formatting for Partial Matches

Excel fuzzy lookup mac