excel - Conditional formatting rows based on cell dynamic

Question

I’m wondering if someone can assist with a conditional formatting issue i just can't wrap my head around

We currently have a table with names in and next to the name a number

Bob 5
Michael 6

Now if i type bob in cell A1 i would like it to highlight that row and the four below it. another example would be if i type Michael in cell A1 it would highlight that row and 5 below that

any help is appreciated

Answer 1

I couldn't write a simpler formula, but that one worked with the data I tested.

A1 will be where names will be inserted.

The table will range from C1 to D7 (assuming there are no column labels).

Select the table C1 to D7 and insert conditional formatting with formula and use the formula:

=AND(COUNTIF(OFFSET(C1,IF(ROW()-VLOOKUP($A$1,$C$1:$D$7,2,0)<=0,1-ROW(),1-VLOOKUP($A$1,$C$1:$D$7,2,0)),0,VLOOKUP($A$1,$C$1:$D$7,2,0)),$A$1)>0,ROW()>=MATCH($A$1,C:C,0))

And pick the formatting your want.

The formula checks two conditions:

COUNTIF(OFFSET(C1,IF(ROW()-VLOOKUP($A$1,$C$1:$D$7,2,0)<=0,1-ROW(),1-VLOOKUP($A$1,$C$1:$D$7,2,0)),0,VLOOKUP($A$1,$C$1:$D$7,2,0)),$A$1)>0

This checks if there is at least 1 match within a designated range depending of the value in D. It will check if there is a match x rows above the current row where x is the value. If there is, the we get the first boolean value (true or false).

The second ROW()>=MATCH($A$1,C:C,0) ensures that the match is above or on the current row.

Google Spreadsheet Demo