# Scan row for value, get column of that match, apply column in another formula in a different cell

``````+------+--------------+--------------+--------------+--------------+--------------+
|      |      A       |      B       |      C       |      D       |      E       |
+------+--------------+--------------+--------------+--------------+--------------+
|   1  |  7/05/2014   |  7/06/2014   |  7/07/2014   |  7/08/2014   |  7/09/2014   |
+------+--------------+--------------+--------------+--------------+--------------+
|   2  |      YES     |     NO       |       NO     |     YES      |      YES     |
+------+--------------+--------------+--------------+--------------+--------------+
|   3  | Yes to date: |      1       |              |              |              |
+------+--------------+--------------+--------------+--------------+--------------+
|   4  | No  to date: |      2       |              |              |              |
+------+--------------+--------------+--------------+--------------+--------------+
``````

Let's say I have a table like above. I want to be able to know the column that matches the current date. So for example, Today is 7/07/2014 - that would return Column C. I'd like to be able to have a function/formula that will dynamically change the range based on which column the current date is in for row 1

Let's say I have a cell that I only want to display data from the start of the range and only until the current day.

So the formula in cell B3 would scan line 1 to determine which column contains the current date - in this case on 7/07/2014 the column is C. So it would scan Line 2 from the beginning column A to column C (A2:C2) where "C" is the variable. And it would "count" the number of cells the word "YES" appears in.

The forumula value would change day-by-day as the date would change and the length of the range it scans should be dynamic based on the date.

I hope this makes sense! If there is any clarification needed please let me know!

• @pnuts - no column A is not sorted. for this spreadsheet no columns will be sorted at any time. Commented Jul 7, 2014 at 16:21
• @pnuts - The row 1 will only contain dates. and they will all be in order. they will not change. What do you mean by "sufficient"? there is no other dates in that column, although I don't know how that's relevant to the question. Commented Jul 7, 2014 at 16:31

The `match` formula will tell you which column within `1:1` matches `today()`; in conjunction with `index` we can use that column number to get the address of the `cell` holding the Yes/No data for today. This becomes useful when paired through string concatenation (`&`) with the address of the first cell of the Yes/No data (`\$A\$2` here) -- the 'dynamic' range you wanted can now be interpreted using the `indirect` formula!
All together, you're looking at the following formula for `B3` in your example:
``````=COUNTIF(INDIRECT("\$A\$2:"&CELL("address",INDEX(\$1:\$2,2,MATCH(TODAY(),\$1:\$1)))),"Yes")
I temporarily forgot about `filter`, which makes this task much easier: `=COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")`