Forum Discussion

GregHastings's avatar
GregHastings
Copper Contributor
Aug 24, 2019

Reverse HLookup?

Hi Folks;

 

I am putting a level scheduling spreadsheet together, where I want to slide schedule hours left and right on the row.  At the top are the dates.  So I want to see where the first occurrence of hours occur in a row (project start), and then look at the date in that column at the top of the page, then put that date in a cell on another page.  I also want to look at the last occurrence of hours in a row and look at the date at the top of that column (project End).

 

With HLookup, I can find a date at the top of the page, and then look at the value in another row, but I can't go the other way.  Any ideas?

 

Thanks,

Greg

Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 24, 2019

    GregHastings 

    Hi Greg,

     

    If that's something like this

    formula for Start date

    =INDEX($A$1:$L$1,0,
    MATCH(1,INDEX(
        ISNUMBER(INDEX($A$2:$L$5,MATCH($B7,$A$2:$A$5,0),0))*
        (INDEX($A$2:$L$5,MATCH($B7,$A$2:$A$5,0),0)>0),
    0),
0))

    for End date

    =LOOKUP(2,1/(INDEX($A$2:$L$5,MATCH($B7,$A$2:$A$5,0),0)>0),$A$1:$L$1)

     

Resources