Excel Worksheet Function: How to refer to a dynamic column

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

Note: Trying without VBA

Let's say I have in C1 the word "Hello"

From another cell, lets say A1, I want to return C:C, because in the first row 1:1, contains the word "Hello"

In A1, I could put:


And this would return 3, as it is in the third column. However, I need it in the format C:C

ADDRESS function can return a specific cell using the column number 3, but not the entire column.

Ultimately, I want to use a COUNTIF(dynamicWholeColumn,criteriaCell)

Any ideas anyone?

Best Answer

You could manipulate the output of ADDRESS using string functions, to turn $C$1 into $C:$C. But if you're putting it into COUNTIF, you'd be better using OFFSET and the output from MATCH.


Related Question