SOLVED
Home

Excel won't read ,, as skip this argument

%3CLINGO-SUB%20id%3D%22lingo-sub-1080892%22%20slang%3D%22en-US%22%3EExcel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1080892%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20weird%20thing%20happen%20with%20my%20Excel.%20It%20doesn't%20read%20%2C%2C%20as%20skip%20this%20argument%2C%20but%20when%20I%20open%20another%20workbook%20have%20already%20use%20the%20same%20formula%20in%20other%20machine%20things%20go%20well.%20Please%20see%20the%20pictures%20for%20more%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20your%20help%20to%20fix%20this%20with%20my%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20All%20%3CLI-EMOJI%20id%3D%22lia_red-heart%22%20title%3D%22%3Ared_heart%3A%22%3E%3C%2FLI-EMOJI%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20791px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163085i32C42419FD69524D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Example%201.PNG%22%20title%3D%22Example%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20673px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163084i8527B135F58CE02D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Example%202.PNG%22%20title%3D%22Example%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1080892%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-1080951%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1080951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F498861%22%20target%3D%22_blank%22%3E%40Bui_Hien_Dat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20probably%20because%20of%20implicit%20intersection.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dvg6_6CENNhI%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dvg6_6CENNhI%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ffastexcel.wordpress.com%2F2016%2F10%2F03%2Fwriting-efficient-vba-udfs-part-14-handling-whole-columns-using-implicit-intersection%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffastexcel.wordpress.com%2F2016%2F10%2F03%2Fwriting-efficient-vba-udfs-part-14-handling-whole-columns-using-implicit-intersection%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1080958%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1080958%22%20slang%3D%22en-US%22%3EThank%20a%20lots%20Lewin.%20I%20see%20it's%20relevant%20to%20my%20case%2C%20but%20I'm%20still%20unable%20to%20find%20solutions%20for%20my%20issues%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1080990%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1080990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F498861%22%20target%3D%22_blank%22%3E%40Bui_Hien_Dat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESkipping%20the%20row_num%20argument%20in%20the%20Index%20formula%20where%20the%20first%20argument%20is%20two%20dimensional%20array%20means%20the%20formula%20will%20return%20an%20array%20i.e.%20more%20than%20one%20value.%3C%2FP%3E%3CP%3EAnd%20if%20you%20have%20excess%20to%20the%20dynamic%20array%20formulas%2C%20your%20formula%20will%20spill%20down%20the%20entire%20array%20down%20the%20rows.%3C%2FP%3E%3CP%3EOtherwise%20you%20will%20need%20to%20confirm%20this%20formula%20with%20Ctrl%2BShift%2BEnter%20which%20will%20return%20the%20top%20item%20from%20the%20array%20returned%20by%20the%20formula.%20And%20I%20don't%20think%20this%20is%20what%20you%20are%20trying%20to%20achieve%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20explain%20what%20exactly%20you%20are%20trying%20to%20achieve%20by%20skipping%20the%20row_num%20argument%20and%20what's%20your%20expected%20output%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20always%20upload%20a%20sample%20workbook%20instead%20of%20an%20image%20of%20the%20data%20to%20get%20a%20quick%20and%20accurate%20answer%20so%20that%20we%20are%20not%20required%20to%20retype%20your%20data%20to%20replicate%20the%20issue%20you%20face.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1081318%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1081318%22%20slang%3D%22en-US%22%3EThanks%20Subodh.%20The%20Ctrl%2BShift%2BEnter%20does%20magic%20to%20my%20formula%2C%20I%20was%20planning%20to%20make%20dependent%20drop%20down%20list%20with%203%20Tier%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1081786%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20won't%20read%20%2C%2C%20as%20skip%20this%20argument%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1081786%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F498861%22%20target%3D%22_blank%22%3E%40Bui_Hien_Dat%3C%2FA%3E!%20Glad%20it%20helped.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi team,

 

There is a weird thing happen with my Excel. It doesn't read ,, as skip this argument, but when I open another workbook have already use the same formula in other machine things go well. Please see the pictures for more information. 

 

Need your help to fix this with my Excel

 

Thank you All Example 1.PNGExample 2.PNG

5 Replies
Highlighted
Thank a lots Lewin. I see it's relevant to my case, but I'm still unable to find solutions for my issues
Solution

@Bui_Hien_Dat 

Skipping the row_num argument in the Index formula where the first argument is two dimensional array means the formula will return an array i.e. more than one value.

And if you have excess to the dynamic array formulas, your formula will spill down the entire array down the rows.

Otherwise you will need to confirm this formula with Ctrl+Shift+Enter which will return the top item from the array returned by the formula. And I don't think this is what you are trying to achieve here.

 

Can you explain what exactly you are trying to achieve by skipping the row_num argument and what's your expected output?

 

Also, please always upload a sample workbook instead of an image of the data to get a quick and accurate answer so that we are not required to retype your data to replicate the issue you face.

Highlighted
Thanks Subodh. The Ctrl+Shift+Enter does magic to my formula, I was planning to make dependent drop down list with 3 Tier
Highlighted

You're welcome @Bui_Hien_Dat! Glad it helped.