Copy array to new field and update to current column

I have an array formula, i think that's what it's called - that finds the most frequent word in a column and returns it while simultaneously ignoring the blank cells and it works totally fine. My problem is when i try to copy it to the next column, the formula stays static, meaning if it's a formula for column C, but now i paste the formula in column D - then hit ctrl+shift+enter it still says column C, so I have to manually type in all of the references to column C and change them to column D to make the formula work in that column. I did it for 5 columns manually and I'm spent. Is there a way I can make this auto populate the new column? Here is my formula:

=INDEX($C$4:$C$33,MATCH(INDEX(MAX(COUNTIFS($C$4:$C$33,$C$4:$C$33,$C$4:$C$33,"<>0"),),),INDEX(COUNTIFS($C$4:$C$33,$C$4:$C$33,$C$4:$C$33,"<>0"),),0))

You can remove the $ symbols in front of every C, then when it is copied across those references to column C will automatically change to D, E etc.

You can use Find & Replace (CTRL-H) to change $C to C in one operation.

Hope this helps.

Pete

This worked perfectly!!! Thanks Pete, you're a lifesaver!

Could you help me with one more thing? What if I wanted to do multiple columns and get the most frequent? For example, I've been doing it with one column, in this example, column C . But what if I wanted to check column C, D, E and look for the most frequent word from those columns combined? How would the formula look then?

It's difficult to advise without seeing your file - it would help if you attached a sample Excel workbook. Take a look at the yellow banner at the top of the screen, for details of how to do this.

Given that you have a formula which works on a single column, I'd be tempted to combine the data from those 3 columns into one (maybe on a different sheet), then you already have a way of achieving your outcome.

Hope this helps.

Pete

k attaching now

Yes, it did work, but I don't have chance to look at it right now, as I will be going out soon (it's near Christmas, so lots of visits/visitors and get-togethers etc.)

Pete