Return name of column

Eric Ernst

Hi,

 

I have a smartsheet with colume names that are acutal dates of a calendar. I am looking for a solutnion to query the name of a specfic column if it has a certain single digit value. (So I get the date back). Any ideals?

 

Charles DESCOTES

To my knowledge the best way to do it is to create a row that repeates the dates in your column field names and work from there.

I am not aware of any way to automate the column headers or the other way around to capture them (e.g. to automatically update the new proposed row). If you can do that please explain.

 

If you create a new row as suggested, then simple test formulas can do the job I assume. Need help on these?

Eric Ernst

Thanks Charles,

 

Then if I would scan past columns of a row to find the first entry I would use the Match() function? And try to get a result from a different row but from the same column with the date reference?

Charles DESCOTES

You might want to combine INDEX and MATCH

 

. INDEX(range, row, column)

INDEX yields the content of a cell determined by its row and column number, i.e. crossing two azimuths a 2D matrix.

 

. MATCH(lookup_value, lookup_range, 0)

MATCH yields the rank of the item in a 1D list (the so called lookup_range).

 

=INDEX([Value $1]2:[Value 50]10, MATCH(target-value*, [Value $1]$1:[Value 50]$1), rank°)

 

* in which 'target-value' can reference a cell where you type the "certain single digit value" you talked a bout initially.

° in which rank ranges between 1 and 49 in this case where only 49 rows are concerned in the 2D matrix. rank can also be the results of a MATCH formula.

 

If your cas is simpler you can use LOOKUP.