Forum Discussion

warwick_Hope's avatar
warwick_Hope
Copper Contributor
May 13, 2019
Solved

Version of Lookup formula?

Have a table dates across the top and items down the side.   The body of the table gets populated with "1s" which auto formats to a cell colour.   Id like to create a column that looks up when a ...
excel
Formulas and Functions
warwick_Hope's avatar
warwick_Hope
Copper Contributor
May 15, 2019

Detlef_Lewin 

If there is more than one cell filled in a row, the formula picks up the cell furthest to the right.

This is fine in the main but just wanted to know if there was any way of changing it to pick the first cell it comes to? (furthest to the left?)

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 16, 2019

    warwick_Hope 

    That would be:

    =INDEX(B$1:Z$1,MATCH(1,B2:Z2,0))

     

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 16, 2019
      I’m just curious. What would be the equivalent formula to achieve the same result using LOOKUP and OUR sexy PI() as the lookup_value argument?
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        May 16, 2019

        Dear Twifoo,

         

        I have no idea. The problem is that LOOKUP() works from right to left.

         

Resources