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:
=MATCH("Hello",1:1,0)
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?
Cheers.
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.=COUNTIF(OFFSET($A:$A,0,MATCH("Hello",1:1,0)-1),"Hello")