Well, this is just getting weird now with that {1}! I've scoured the interenets and can't seem to figure out why these N and T functions are required. I do suspect, however, that the IF function forces every function in which it is nested to evaluate for each iteration, and that is why this 'hack' of functions like INDEX succeeds. It's very useful. On another note, I've discovered that, when in edit mode, a formula can be evaluated with f9 by simply clicking at the end or before the equals sign - this eliminates the need for highlighting the entire formula prior to pressing f9. Awesome stuff again, Mike!
Hi Mike, Thanks a lot for all these videos! I have learnt a ton. In fact I was trying to use the below formula and I seem to be getting a #VALUE error. =SUMIFS($G$2:$G$56,INDEX($B$2:$B$15,N(IF(1,MATCH($E$2:$E$56,$A$2:$A$15,0)))),J12) This essentially translates to : =SUMIFS({38.7;45.21;68.16;100.98;41.16;22.91;61.11;46.53;22.38;45.21;45.44;22.95;78.6;44.75;79.95;22.95;19.95;306;23.5;68.74;102;156.7;23.15;44.52;491.4;42;68.74;68.39;45.9;79.95;22.8;70.5;343.69;46.53;68.85;69.8;102;94;59.25;22.61;68.16;41.58;159.9;22.91;159.9;67.47;78.35;45.21;67.13;45.9;45.9;23.5;33.32;258.5;39.9},{"West";"West";"South";"West";"MidWest";"MidWest";"East";"MidWest";"West";"MidWest";"South";"West";"East";"East";"MidWest";"East";"MidWest";"MidWest";"MidWest";"West";"West";"West";"MidWest";"West";"East";"East";"East";"East";"MidWest";"MidWest";"South";"West";"East";"West";"West";"East";"West";"West";"West";"West";"MidWest";"MidWest";"East";"South";"East";"West";"MidWest";"South";"West";"West";"MidWest";"South";"South";"MidWest";"West"},"South") Does this formula not work with SUMIFS?
Why, oh why do I love your videos, Mike? Hahahaha that curly bracket enclosing 1 inside IF for the T function is just crazy...my excel buddies sure are gonna love this.
@@excelisfun Hello Mr Mike :) ExcelIsFun =VLOOKUP(N(IF(1;H14#));H14:J23;{2\3};0) I tried using those technics from EMT 1304,05,06 to my problem with vlookup (you recently replied to those), and i used like above. Weirdly enough I got my result (returning 2 columns), but oddly enough it result is exactly the same as I would use: =VLOOKUP(H14;H14:J23;{2\3};0), So yeah its weird how functions works / not work with arrays, especially dynamic ones
Thanks for this mystical formuła.!!! T (N) doesn't work directly with real range but works with arrays. As a result of this T(IF(1,$E$2:$E$56)) you can get array_referenced_to_cells but not array_referenced_to_values and this is a key (and in my opinion this is conected to registered type of argument - i'm 90% sure) So these below are equivalent T(IF(1,$E$2:$E$56&"")) = T(IF(1,$E$2:$E$56)&"") = T(IF({1},$E$2:$E$56)) First two required CSE but the last one doesn't (because of constant array {1} ) Try T($E$2:$E$56) and T($E$2:$E$56&"")
I stumbled on this while searching for an answer to a problem I have. Unfortunately, it didn't solve the problem, and conventional wisdom states what I want to do cannot be done. However, I thought I'd ask here anyway. So the problem... When using MATCH, it expects a contiguous row or column e.g. A1:H1 or A1:A8 . Is it possible to use this trick or something similar to parse a non-contiguous list of cells and make Excel think they are a row or column. In my specific case, the cells are on a diagonal A1,B2,C3,&c...
Salam dari Indonesia!First, thank you so much for the trick & other video tricks, so helpful. Second, I know it's not relevant to the video, but have you ever done an accounting cycle in a single video (from transaction to closing) ? If not, could you please make one? I'm sure it's gonna be awesome with all of the Excel tricks and all. Again, thank you!!!
Despite how awesome SUMPRODUCT & LOOKUP is I also had the same question "What if lookup values aren't sorted??". A practical concern due to spreadsheets being used by the masses (errors constantly an issue). Of course I knew you would find an answer to this Mike! The SUMPRODUCT & VLOOKUP & T(IF({1} is amazing! Difficult to decipher for beginners but still amazing how it works and how the online array experts discover these things! www.excelxor.com is a wealth of knowledge!
You are doing awesome work.. Really it helps me a lot in my office work.. In excel you are magician..
I am glad that the videos help you!
Well, this is just getting weird now with that {1}! I've scoured the interenets and can't seem to figure out why these N and T functions are required. I do suspect, however, that the IF function forces every function in which it is nested to evaluate for each iteration, and that is why this 'hack' of functions like INDEX succeeds. It's very useful. On another note, I've discovered that, when in edit mode, a formula can be evaluated with f9 by simply clicking at the end or before the equals sign - this eliminates the need for highlighting the entire formula prior to pressing f9. Awesome stuff again, Mike!
The mystery lives on!!!!
Hi Mike, Thanks a lot for all these videos! I have learnt a ton. In fact I was trying to use the below formula and I seem to be getting a #VALUE error.
=SUMIFS($G$2:$G$56,INDEX($B$2:$B$15,N(IF(1,MATCH($E$2:$E$56,$A$2:$A$15,0)))),J12)
This essentially translates to :
=SUMIFS({38.7;45.21;68.16;100.98;41.16;22.91;61.11;46.53;22.38;45.21;45.44;22.95;78.6;44.75;79.95;22.95;19.95;306;23.5;68.74;102;156.7;23.15;44.52;491.4;42;68.74;68.39;45.9;79.95;22.8;70.5;343.69;46.53;68.85;69.8;102;94;59.25;22.61;68.16;41.58;159.9;22.91;159.9;67.47;78.35;45.21;67.13;45.9;45.9;23.5;33.32;258.5;39.9},{"West";"West";"South";"West";"MidWest";"MidWest";"East";"MidWest";"West";"MidWest";"South";"West";"East";"East";"MidWest";"East";"MidWest";"MidWest";"MidWest";"West";"West";"West";"MidWest";"West";"East";"East";"East";"East";"MidWest";"MidWest";"South";"West";"East";"West";"West";"East";"West";"West";"West";"West";"MidWest";"MidWest";"East";"South";"East";"West";"MidWest";"South";"West";"West";"MidWest";"South";"South";"MidWest";"West"},"South")
Does this formula not work with SUMIFS?
Why, oh why do I love your videos, Mike? Hahahaha that curly bracket enclosing 1 inside IF for the T function is just crazy...my excel buddies sure are gonna love this.
Glad it helps!
Mind blown! Thanks for this - I've several sheets where this is immediately applicable.
Yes, me too, I love the trick!
@@excelisfun Hello Mr Mike :) ExcelIsFun
=VLOOKUP(N(IF(1;H14#));H14:J23;{2\3};0)
I tried using those technics from EMT 1304,05,06 to my problem with vlookup (you recently replied to those), and i used like above.
Weirdly enough I got my result (returning 2 columns), but oddly enough it result is exactly the same as I would use:
=VLOOKUP(H14;H14:J23;{2\3};0),
So yeah its weird how functions works / not work with arrays, especially dynamic ones
Wow...This is breakthrough knowledge. Thanks for explaining Mike.
You are welcome1 It is breakthrough for me too! : ) It is great to be on such a great Online Excel Team!!!
Great trick. Thanks for sharing it.
You are welcome! It is great to be on a good team!
Thanks for this mystical formuła.!!!
T (N) doesn't work directly with real range but works with arrays. As a result of this
T(IF(1,$E$2:$E$56))
you can get array_referenced_to_cells but not array_referenced_to_values and this is a key (and in my opinion this is conected to registered type of argument - i'm 90% sure) So these below are equivalent
T(IF(1,$E$2:$E$56&"")) = T(IF(1,$E$2:$E$56)&"") = T(IF({1},$E$2:$E$56))
First two required CSE but the last one doesn't (because of constant array {1} )
Try T($E$2:$E$56) and T($E$2:$E$56&"")
Awesome notes and tips!!!!
I stumbled on this while searching for an answer to a problem I have. Unfortunately, it didn't solve the problem, and conventional wisdom states what I want to do cannot be done. However, I thought I'd ask here anyway.
So the problem... When using MATCH, it expects a contiguous row or column e.g. A1:H1 or A1:A8 .
Is it possible to use this trick or something similar to parse a non-contiguous list of cells and make Excel think they are a row or column.
In my specific case, the cells are on a diagonal A1,B2,C3,&c...
Just to clarify: I didn't write the blog post. I just posted the link to the blog post.
Yes, I knew that. I think I said that in the video... : )
Absolutately Greattttttttttttttttttttttttt
Glad you like it!
Salam dari Indonesia!First, thank you so much for the trick & other video tricks, so helpful. Second, I know it's not relevant to the video, but have you ever done an accounting cycle in a single video (from transaction to closing) ? If not, could you please make one? I'm sure it's gonna be awesome with all of the Excel tricks and all. Again, thank you!!!
I do not have videos on that. Sorry...
always thumbs up Mike!
Thanks, Kevin!!!
Wonders. Thanks
You are welcome!
Despite how awesome SUMPRODUCT & LOOKUP is I also had the same question "What if lookup values aren't sorted??". A practical concern due to spreadsheets being used by the masses (errors constantly an issue). Of course I knew you would find an answer to this Mike! The SUMPRODUCT & VLOOKUP & T(IF({1} is amazing! Difficult to decipher for beginners but still amazing how it works and how the online array experts discover these things! www.excelxor.com is a wealth of knowledge!
Yes, www.excelxor.com really is an amazing Array Formula Knowledge site!!!
WOW!
: )
4:48 6:12
That is a wild one!!!!!
@@excelisfun I agree :)