Need an HLOOKUP or similar

Does anyone have a formula that will evaluate all the cells in a row, then return the column name for the cell that has a particular value?

Here's what I am trying to do. I have a Smartsheet with 23 columns, of which 5 are due dates. A 6th date column is "Next Due Date" and contains a formula that evaluate the other 5 date columns and returns the next one that is upcoming but not past. I would like to write an additional formula that lets me know what column that date comes from. For example, my existing formula lets me know that the "Next Due Date" is 12/15/18, and the needed formula would tell me that 12/15/18 is in the "Proposal Due" or "Interim Report" or "Final Report" column.

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can't pull a column name in Smartsheet directly like that, you have to add a row (I usually go ahead and put it up top) and re-enter the column names across that top row. You can then use an INDEX/MATCH set-up to reference [Column Name]$1. This will pull whatever data is in the first row of the column which ends up being the column name that you manually entered.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!