Index function not working

%3CLINGO-SUB%20id%3D%22lingo-sub-1246183%22%20slang%3D%22en-US%22%3EIndex%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246183%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20table%20of%20postcodes%20in%20the%20UK%20and%20times%20that%20a%20vehicle%20can%20first%20reach%20in%20the%20top%20row%20as%20my%20header.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20used%20a%20left%20function%20to%20break%20down%20the%204%20possibilities%20from%20the%20postcode%20(ph42%20becomes%20p%2C%20pH%2C%20ph4%20and%20ph42%20which%20I%20can%20use%20an%20if%20function%20on%20later)%20but%20my%20array%20function%20is%20not%20returning%20the%20correct%20headers%20and%20returning%20ones%20which%20are%20not%20related%20at%20all.%20The%20formula%20is%20below%3CBR%20%2F%3E%3CBR%20%2F%3E%3DINDEX(%24A%241%3A%24J%241%2C%2CMIN(IF(%24A%242%3A%24J%241048576%3DM6%2CCOLUMN(A%3AJ))))%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20has%20worked%20on%20a%20similar%20spreadsheet%20but%20not%20on%20this%20one%20and%20I%20cannot%20work%20out%20why.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1246183%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-1246213%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590830%22%20target%3D%22_blank%22%3E%40Ashleighb2437%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20provide%20sample%20file%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246252%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246252%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%3EPlease%20see%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246309%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590830%22%20target%3D%22_blank%22%3E%40Ashleighb2437%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20no%20matches%20IF()%20returns%20FALSE%2C%20taking%20MIN()%20we%20receive%20zero%2C%20INDEX%20returns%20entire%20row%20A1%3AJ1%2C%20with%20non-dynamic%20array%20Excel%20it%20returns%20first%20element%20of%20it%2C%20i.e.%2009%3A00%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20modify%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24A%241%3A%24J%241%2C%2CMIN(IF(%24A%242%3A%24J%241048576%3DM6%2CCOLUMN(A%3AJ)%2C1E%2B32)))%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eresult%20is%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%20324px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178843i3E137F5DAB6CA1B2%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%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246314%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246314%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590830%22%20target%3D%22_blank%22%3E%40Ashleighb2437%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20you%20can%20try%20writing%20the%20formula%20as%20below.%20When%20it%20don't%20find%20the%20value%20it%20will%20have%20%23value%20error%26nbsp%3B%3C%2FP%3E%3CP%3Ewith%20earlier%20formula%20when%20add%20the%20min%20if%20it%20don't%20found%20the%20value%20it%20return%20as%200%20which%20returns%20the%20entire%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24A%241%3A%24J%241%2CIF(MIN(IF(M12%3D%24A%242%3A%24J%24400%2CCOLUMN(%24A%241%3A%24J%241)))%3D0%2C%22%22%2CMIN(IF(M12%3D%24A%242%3A%24J%24400%2CCOLUMN(%24A%241%3A%24J%241)))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%3C%2FSPAN%3E.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246356%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20function%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590830%22%20target%3D%22_blank%22%3E%40Ashleighb2437%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%2C%20and%20more%20safe%20variant%2C%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%0A%20%20%20SUMPRODUCT((%24A%241%3A%24J%2410000%3DM6)*COLUMN(%24A%241%3A%24J%2410000))%2C%0A%20%20%20INDEX(%24A%241%3A%24J%241%2C1%2CSUMPRODUCT((%24A%241%3A%24J%2410000%3DM6)*COLUMN(%24A%241%3A%24J%2410000)))%2C%0A%20%20%20%22no%20such%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Hi,

I have a table of postcodes in the UK and times that a vehicle can first reach in the top row as my header.

I have used a left function to break down the 4 possibilities from the postcode (ph42 becomes p, pH, ph4 and ph42 which I can use an if function on later) but my array function is not returning the correct headers and returning ones which are not related at all. The formula is below

=INDEX($A$1:$J$1,,MIN(IF($A$2:$J$1048576=M6,COLUMN(A:J))))

It has worked on a similar spreadsheet but not on this one and I cannot work out why.

Thanks
5 Replies
Highlighted

@Ashleighb2437 

Could you provide sample file?

Highlighted

@Sergei BaklanPlease see attached file

Highlighted

@Ashleighb2437 

If no matches IF() returns FALSE, taking MIN() we receive zero, INDEX returns entire row A1:J1, with non-dynamic array Excel it returns first element of it, i.e. 09:00

 

You may modify as

=IFERROR(INDEX($A$1:$J$1,,MIN(IF($A$2:$J$1048576=M6,COLUMN(A:J),1E+32))),"no such")

result is

image.png 

Highlighted

HI @Ashleighb2437 

 

May you can try writing the formula as below. When it don't find the value it will have #value error 

with earlier formula when add the min if it don't found the value it return as 0 which returns the entire row. 

 

=INDEX($A$1:$J$1,IF(MIN(IF(M12=$A$2:$J$400,COLUMN($A$1:$J$1)))=0,"",MIN(IF(M12=$A$2:$J$400,COLUMN($A$1:$J$1)))))

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

Highlighted

@Ashleighb2437 

Another, and more safe variant, could be

=IF(
   SUMPRODUCT(($A$1:$J$10000=M6)*COLUMN($A$1:$J$10000)),
   INDEX($A$1:$J$1,1,SUMPRODUCT(($A$1:$J$10000=M6)*COLUMN($A$1:$J$10000))),
   "no such"
)