SOLVED

Holiday list - Index with dynamic row

%3CLINGO-SUB%20id%3D%22lingo-sub-1167357%22%20slang%3D%22en-US%22%3EHoliday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167357%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20with%20%22from%22%20and%20%22until%22%20dates%20(column%20A%20and%20B).%20In%20the%20column%20C%2C%20I%20have%20the%20type.%3C%2FP%3E%3CP%3ENext%20to%20that%20list%20I%20have%20a%20date%20list%20with%20dates%20from%20the%201st%20to%20the%20last%20day%20in%20the%20current%20month.%20Now%2C%20I%20want%20the%20type%20from%20the%20related%20row%20in%20that%20list.%20How%20can%20I%20receive%20the%20row%20in%20the%20function%20%22INDEX%22%20as%20a%20dynamic%20value%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel.png%22%20style%3D%22width%3A%20745px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F170488i54E940206790D2D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excel.png%22%20alt%3D%22excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1167357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167677%22%20slang%3D%22en-US%22%3ERe%3A%20Holiday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552115%22%20target%3D%22_blank%22%3E%40MrBond%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERelated%20row%20will%20be%20%3CEM%3EROW()-ROW(%24A%241)%3C%2FEM%3E.%20But%20I'm%20not%20sure%20what%20you'd%20like%20to%20calculate%2C%20perhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%20(D%241%26gt%3B%3D%24A3)*(D%241%26lt%3B%3D%24B3)%2C%22U%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167718%22%20slang%3D%22en-US%22%3ERe%3A%20Holiday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20get%20the%20type.%20So%2C%20if%20there%20is%20for%20example%20the%20value%20%22G%22%20in%20C3%2C%20there%20must%20be%20a%20%22G%22%20in%20the%20cells%20F2%20and%20G2.%3C%2FP%3E%3CPRE%3E%3DINDEX(%24A%242%3A%24C%243%3BIF(%20(D%241%26gt%3B%3D%24A3)*(D%241%26lt%3B%3D%24B3)%3B%22U%22%3B%22%22)%3B3)%20%3C%2FPRE%3E%3CP%3Ereturns%20the%20error%20%22%23VALUE%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167858%22%20slang%3D%22en-US%22%3ERe%3A%20Holiday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552115%22%20target%3D%22_blank%22%3E%40MrBond%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20717px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F170590i3990D83A73D9CFB2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(SUMPRODUCT((D%241%26gt%3B%3D%24A%242%3A%24A%244)*(D%241%26lt%3B%3D%24B%242%3A%24B%244))%2C%0A%20%20%20%20INDEX(%24C%242%3A%24C%244%2C%0A%20%20%20%20%20%20SUMPRODUCT((D%241%26gt%3B%3D%24A%242%3A%24A%244)*(D%241%26lt%3B%3D%24B%242%3A%24B%244)*(ROW(%24A%242%3A%24A%244)-ROW(%24A%241)))%0A%20%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1170410%22%20slang%3D%22en-US%22%3ERe%3A%20Holiday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1170410%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1170696%22%20slang%3D%22en-US%22%3ERe%3A%20Holiday%20list%20-%20Index%20with%20dynamic%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1170696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F552115%22%20target%3D%22_blank%22%3E%40MrBond%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi! 

I have a list with "from" and "until" dates (column A and B). In the column C, I have the type.

Next to that list I have a date list with dates from the 1st to the last day in the current month. Now, I want the type from the related row in that list. How can I receive the row in the function "INDEX" as a dynamic value?

excel.png

5 Replies

@MrBond 

Related row will be ROW()-ROW($A$1). But I'm not sure what you'd like to calculate, perhaps

=IF( (D$1>=$A3)*(D$1<=$B3),"U","")

 

@Sergei Baklan 

I'd like to get the type. So, if there is for example the value "G" in C3, there must be a "G" in the cells F2 and G2.

=INDEX($A$2:$C$3;IF( (D$1>=$A3)*(D$1<=$B3);"U";"");3) 

returns the error "#VALUE".

best response confirmed by MrBond (New Contributor)
Solution

@MrBond 

If like this

image.png

that could be

=IF(SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)),
    INDEX($C$2:$C$4,
      SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)*(ROW($A$2:$A$4)-ROW($A$1)))
    ),
"")
Thank you very much!

@MrBond , you are welcome