Numbers: Change cell color based on text in another cell

First, change the formula in cell C9 to:

=XLOOKUP(D9,A,B,"",0)

This is a much more concise and reliable way of finding the rider's name - it looks in the adjacent cell D9 to find the 'Rider x' value and looks that up in Column A. When it finds a result it returns the corresponding value from column B.

In this way it's performing a dynamic lookup - if you ever added a 'Rider 5', you'd have to update every cell in your original formula to have an IF(D9="Rider 5"...) clause, but with this XLOOKUP it's dynamic and automatic.

That said, the simplest solution to your ask is via Conditional Formatting (Inspector -> Cell -> Conditional Highlighting.

There are two elements to Conditional Formatting you might be missing - first is that you can have any number of conditions to check, the second is how you get it to look at a dynamic value rather than a static one.

In this case, I set a series of rules on cell C9 that look like:

The trick is getting it to reference cells in column B. To do this, you need to click the little green icon that appears at the right hand end of the value field:

This lets you click on another cell in the table to use as a reference (otherwise typing in, say, 'B2' would look for the literal string "B2" rather than the contents of cell B2). Just make sure you click the green check mark to save the change (hitting return/enter won't do it):

Then click on the cell reference and turn on Preserve Row and Preserve Column, so that it always references these exact cells.

So now you can select from the popup menu in column D to select a rider number. That will initiate a XLOOKUP in column C to find the rider's name, which in turn will trigger the Conditional Highlighting to choose the appropriate color,

Optional: One other change I would make is to put the Rider number/names into a separate table. The XLOOKUP() will still work the same, but it helps to separate out the different classes of data (one table for the riders, another table for the Run):

This has the slight speed benefit - since the XLOOKUP() only has to scan column A in the smaller Riders table, and it's also easier to add new riders without having to shuffle the runs around, inserting additional rows, etc.

Having everything in a single table is the Excel-ish way of doing things (although even Excel now offers to separate tables within a sheet, but not in quite such an elegant manner as Numbers does)

> Is it possible to match the colour with the rider in Column E but still allowing me to enter a number e.g. as shown in E4 (manually inserted colour to show you what I want

Oh boy.... yes... but...

The single biggest drawback with Numbers is its weak Conditional Formatting. It is not possible to have Numbers perform formatting based on some arbitrary other cells (e.g. IF(C4="Rob"...) ).

Instead, the typical hack/workaround is to create a separate 'helper' column to perform the logical check, then based the conditional highlighting off that cell. It's cumbersome enough when there is one value, but exponentially so when there are multiple (e.g. "is it Dave? Rob? Johnny? or Andy?").

So, ordinarily, you'd add one new column (e.g. F) that performs the logic check (e.g. C4="Rob"). If that returns TRUE, then you copy the value from the target cell into this cell. Then you can use normal Conditional Highlighting to compare the value in E to the value in F and highlight accordingly.

Unfortunately, since you have multiple possible values ("Dave", "Rob", "Johnny", and "Andy"), you need a helper column for each of them. The cells in these columns will be blank, unless the Rider name (column C) matches the column header, in which case it returns the Mileage (Column E).

An amended version of my table now looks like this:

Where you can see I've added 4 additional columns for the various riders (you'll need to expand this to cover the number of riders you have).

The relevant formulas are:

F1:

=TOROW(Riders::B,3,FALSE)

This pulls all the Rider names from the Riders table into the column headers (this guarantees there are no typos or missing values, and it will automatically expand/shrink to accommodate any changes to the riders list.

then, in cell F2:

=IF($C2=F$1,$E2,"")

This compares $C2 (the rider name) to F$1 (the name at the top of this column). If they match, it copies the Value from the Mileage column. If it doesn't match, we leave it blank.

Fill this formula across and down, and it should fill out the matrix such that each rider's mileage is copied into the corresponding column.

Now you have something that Conditional Highlighting can compare against. For each cell in the Mileage column you check if it matches the value in the various riders' columns, and can set the highlighting accordingly:

Once set you can hide the 'helper' columns since they're not needed for the final output.

Unfortunately, this is made more complex that simply being able to write an arbitrary IF() statement within the Conditional Highlighting field, but that's the deck we've been dealt (feel free to petition Apple for better Conditional Highlighting features via Feedback - Numbers - Apple :) )

Hi alig31,

Another way to highlight cells based on text in another cell. Kudos to Badunit for this "Text does not end with" solution: How can I make row colour change as per v… - Apple Community

Here is my adaptation of Badunit's idea.

Formula in "Helper Cell" F2 is IF($C2=F$1,"~","")

That inserts either the tilde (~) character, or NULL.

~ is unlikely to occur elsewhere in your table. Or use another unusual character.

Fill right and down to all "Helper Cells" (F2 to I5 in this example).

Conditional Highlighting. Select the cells you want. I selected cells C2 to E5.

Numbers will automatically apply these rules to other rows that you have selected.

The Charm of Numbers!

Regards,

Ian.

> I think it overcomplicates a simple spreadsheet...

I 100% agree. This kind of ask (set conditional formatting on this cell based on some other cell's value) is very common, and not handled well within Numbers.

The workaround for a single comparison using a helper column isn't too intrusive (even if it's not entirely intuitive), but it gets way more complicated if there are several conditions to consider.

I think the complication here is that you need four 'helper' columns (or more if the real use case has more names than used in this example).

Granted, the approach of 'does not end with' allows you to cover a range of cells with a single rule, whereas my example was based on adding a single additional column since the name was already handled.

As novel and as neat as the 'helper' column approach is, it pales in comparison to being able to use an arbitrary IF() function (or other boolean calculation) in the Conditional Highlighting setup, and that's exactly what most people expect to be able to do.