Function to increase by cloumn and Row simultaneously

hi Guys

i have a function:
=IF(AND(J6="Yes",COUNTIF(C54:H54,C6)>0),C6)

at the moment this function searches for matching values in C6 within range C54:H54 fine based on condition in J6

when i move to row C55:H55 i want the condition to also move to coloumn K...Is this possible? or do i have to go VBA...ideally i would then paste this in surrounding cells

So what would it look like in row 55? Presumably C54:H54 becomes C55:H55, what about C6, does that stay the same?

Originally Posted by daddylonglegs

So what would it look like in row 55? Presumably C54:H54 becomes C55:H55, what about C6, does that stay the same?

thanx for the quick reply. Yes, C6 remains the same, in reality the range is C5:H30 and this remains the same and is of same length as the rows