# Formula that moves down a row when you move right a column

Occasional Visitor

# Formula that moves down a row when you move right a column

In columns C-G I have certain letters, and I want to see whether any letters in row 3 match those in rows 3-100, any in row 4 match those in 3-100 etc. I've sent up the following formula (this is from J4, which will see if any in row 3 match row 5, and I can drag this down column H so that it always compares the row with row 5):

=COUNTIF(\$C3:\$G3,\$C\$5)+COUNTIF(\$C3:\$G3,\$D\$5)+COUNTIF(\$C3:\$G3,\$E\$5)+COUNTIF(\$C3:\$G3,\$F\$5)+COUNTIF(\$C3:\$G3,\$G\$5)

(There's probably a simpler way to do this!)

However, I then want to drag the equation over to column K, and so on, and make it always look at the next row down, so K always checks against 6C-G, L always against 7C-G and so on. I don't know how to make it so that it works when I drag it down, but also works when I drag it across.

If possible, I'd also love to see if sequences are the same in other rows. For example, if one row contains A B C D E, I'd like to see if others contain A B in the same position, or ABC, BCD, CDE, DE etc.

I hope that makes sense! Thank you for even getting this far!