Excel won't read ,, as skip this argument

[Bui_Hien_Dat]

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 <3

[#2]

@Bui_Hien_Dat 

That is probably because of implicit intersection.

http://www.youtube.com/watch?v=vg6_6CENNhI

https://fastexcel.wordpress.com/2016/10/03/writing-efficient-vba-udfs-part-14-handling-whole-columns...

[#3]

Thank a lots Lewin. I see it's relevant to my case, but I'm still unable to find solutions for my issues :(

[#4 · Accepted 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.

[#5]

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

[#6]

You're welcome @Bui_Hien_Dat! Glad it helped.