Home

Reverse HLookup?

%3CLINGO-SUB%20id%3D%22lingo-sub-821490%22%20slang%3D%22en-US%22%3EReverse%20HLookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821490%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20putting%20a%20level%20scheduling%20spreadsheet%20together%2C%20where%20I%20want%20to%20slide%20schedule%20hours%20left%20and%20right%20on%20the%20row.%26nbsp%3B%20At%20the%20top%20are%20the%20dates.%26nbsp%3B%20So%20I%20want%20to%20see%20where%20the%20first%20occurrence%20of%20hours%20occur%20in%20a%20row%20(project%20start)%2C%20and%20then%20look%20at%20the%20date%20in%20that%20column%20at%20the%20top%20of%20the%20page%2C%20then%20put%20that%20date%20in%20a%20cell%20on%20another%20page.%26nbsp%3B%20I%20also%20want%20to%20look%20at%20the%20last%20occurrence%20of%20hours%20in%20a%20row%20and%20look%20at%20the%20date%20at%20the%20top%20of%20that%20column%20(project%20End).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20HLookup%2C%20I%20can%20find%20a%20date%20at%20the%20top%20of%20the%20page%2C%20and%20then%20look%20at%20the%20value%20in%20another%20row%2C%20but%20I%20can't%20go%20the%20other%20way.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-821490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821522%22%20slang%3D%22en-US%22%3ERe%3A%20Reverse%20HLookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397436%22%20target%3D%22_blank%22%3E%40GregHastings%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Greg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20733px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128363i30D047ABD516DAA6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20for%20Start%20date%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24A%241%3A%24L%241%2C0%2C%0A%20%20%20%20MATCH(1%2CINDEX(%0A%20%20%20%20%20%20%20%20ISNUMBER(INDEX(%24A%242%3A%24L%245%2CMATCH(%24B7%2C%24A%242%3A%24A%245%2C0)%2C0))*%0A%20%20%20%20%20%20%20%20(INDEX(%24A%242%3A%24L%245%2CMATCH(%24B7%2C%24A%242%3A%24A%245%2C0)%2C0)%26gt%3B0)%2C%0A%20%20%20%200)%2C%0A0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20End%20date%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(2%2C1%2F(INDEX(%24A%242%3A%24L%245%2CMATCH(%24B7%2C%24A%242%3A%24A%245%2C0)%2C0)%26gt%3B0)%2C%24A%241%3A%24L%241)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
GregHastings
Occasional Visitor

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

1 Reply

@GregHastings 

Hi Greg,

 

If that's something like this

image.png

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)

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies