Excel Magic Trick 1306: Simulate VLOOKUP Helper Column In Array Formula using T(IF({1},Array) Trick

Download Excel Files:

Learn how to create a formula that simulates a relationship between two tables (like a VLOOKUP Helper Column) to create a Regional Report. See array formula where we see VLOOKUP function and Exact Match to deliver an Array of Items using T(IF({1},Array) Trick.
Thanks to Bill Szysz and XLarium at YouTube and to this amazing blog:
Related Video: Excel Magic Trick 1085: How To Simulate VLOOKUP Helper Column In Array Formula (Number or Text)

[ashishgupta]

You are doing awesome work.. Really it helps me a lot in my office work.. In excel you are magician..

[Laydjo]

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.

[Sal_A]

Wow...This is breakthrough knowledge.  Thanks for explaining Mike.

[drsteele]

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!

[kossafication]

Mind blown! Thanks for this - I've several sheets where this is immediately applicable.

[kutra]

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?

[BillSzysz]

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&"")

[SuryaBudimansyah]

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

[XLarium]

Just to clarify: I didn't write the blog post. I just posted the link to the blog post.

[CmdrBondMI]

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

[MySpreadsheetLab]

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!