# Highlight value in column based on list of values in other column that's on the same line as another value

Is that subject confusing enough?

It's for calculating speed@RPM for an engine and transmission combination. I want to highlight specific cells. For example, in column C (2nd gear), I want to highlight the closest speed that matches but does not exceed the speed at the Shift RPM in column B (1st gear). Basically, what speed and RPM will I be at immediately after a shift? So right now, I'd want cell C30 (50.60) to be highlighted because cell B56 (51.44) is at the Shift RPM. And cell D35 (85.12) should be highlighted because cell C56 (86.16) is at the Shift RPM. I need this to happen for columns C through G (Gear 2 through Gear 6).

HOWEVER, Shift RPM can be anything that I set in cell I20, and that number gets rounded (up or down) to the nearest 100RPM when the spreadsheet highlights the Shift RPM row. So whatever formula determines which cell to highlight also needs to round (up or down) to the nearest 100RPM from whatever is entered in cell I20 to determine which cell needs to be highlighted. I currently do the rounding by choosing the value in column A that's closest to the value entered in I20, and that's been working well.

I'm fine with having a separate conditional formatting rule for each column, in fact that's how I currently do a lot of things,like highlighting target cruising speeds. It's not ideal or efficient, but I'm ok with it as long as it just works.

I have a half-hearted crazy attempt at making this work in column C, but it's wild and makes no sense It looks like so:

``````=AND(C3<>"",QUERY(\$A\$3:\$B\$990, "SELECT B WHERE A = index(\$A\$3:\$A,match(min(ABS(\$I\$20-\$A\$3:\$A)),ABS(\$I\$20-\$A\$3:\$A),0))")<>"",\$C\$3=SORTN(\$C\$3:\$C,1,0,ABS(\$C\$3:\$C-I\$20),1))
``````

But obviously that doesn't accomplish anything. Help would be greatly appreciated!

I found that I had to create a range where I could calculate the various speed@RPM outcomes per gear. And then use this data as references for the conditional formatting. When I tried to include the relevant formulas directly into the conditional formatting rules, I found that the rules could not be saved.

Note that I made this as dynamic as possible by including the data start and end row, as well as the column numbers for each of the gears.

Calculations

There are two calculation for each set of gears:

• the value for the speed at a given RPM in a given gear (in this case, 6300 RPM)
• the closest matching speed for the next gear

Starting gear formula

• 1st: `=vlookup(\$I\$20,\$A\$3:\$G\$193,K\$3,1)` equals 51.44
• 2nd: `=vlookup(\$I\$20,\$A\$3:\$G\$193,L\$3,1)` equals 86.16
• 3rd: `=vlookup(\$I\$20,\$A\$3:\$G\$193,M\$3,1)` equals 127.69
• 4th: `=vlookup(\$I\$20,\$A\$3:\$G\$193,N\$3,1)` equals 170.25
• 5th: `=vlookup(\$I\$20,\$A\$3:\$G\$193,O\$3,1)` equals 218.27

Closest matching speed

• 2nd: `=vlookup(K5,indirect(address(\$L\$1,L\$3,4)&":"&address(\$L\$2,L\$3,4)),1,1)` equals 50.60
• 3rd: `=vlookup(L7,indirect(address(\$L\$1,M\$3,4)&":"&address(\$L\$2,M\$3,4)),1,1)` equals 85.12
• 4th: `=vlookup(M9,indirect(address(\$L\$1,N\$3,4)&":"&address(\$L\$2,N\$3,4)),1,1)` equals 127.01
• 5th: `=vlookup(N11,indirect(address(\$L\$1,O\$3,4)&":"&address(\$L\$2,O\$3,4)),1,1)` equals 169.76
• 6th: `=vlookup(O13,indirect(address(\$L\$1,P\$3,4)&":"&address(\$L\$2,P\$3,4)),1,1)` equals 217.64

[

Conditional Formatting rules

Conditional Formatting

• OMG you rock, that works better than I hoped. StackExchange needs a tipping feature to thank people like you Commented Jan 30, 2022 at 3:28