Excel Magic Trick 1305: Adding Multiple Two-Way Lookups INDEX function & N(IF(1,Array) Trick

Download Excel Files:

Learn how to perform multiple two-way lookups for a cross tabulated table to get multiple sales numbers and then add them using an array formula. Learn how to get INDEX function to deliver an Array of Items using N(IF(1,Array) Trick.
Thanks to Bill Szysz and XLarium at YouTube and to this amazing blog:
See how to get INDEX to return an array of values
INDEX function to deliver an Array of Items using N(IF(1,Array) Trick

[deninsrmic]

Both Formulas are indeed great and deliver precise result. Drawback from the latter is that it is much longer than the former but more intelligible and cogent in its way of construing it. Yes the former is shorter but not cogent and indeed one hardly can fathom as to why this construct of formula N(IF(1… does what it does. Good to know that one can use both ways of getting the same result. Link provided in tutorial to advance Excel is awesome.Thanks.

[ronyshafiq]

Your Xcel vedio make me extra ordinary in my new thanks

[wayneedmondson]

Hi Mike.. was researching something and came across a reference to the N(IF(1, array)) trick which was new to me. Your example above explains it perfectly. Thanks for another hidden gem :)) Thumbs up!!

[rrrprogram]

As usual ... Excellent one ... Super like

[justinacciavatti]

Super rad stuff, Mike - thanks for the knowledge

[levelzeroD]

You make me master my excels ExcelisFun

[mohamedchakroun]

Crazyyyy but Very very very awsome bigboss

[jazzista]

MIke: thanks for posting this video. I am working on a project that I need something like this to determine total expenditures ITD. I have never seen the N function wrapped with IF and the number .

[sim]

It's an offbeat one. Really cool.

[jasongot]

Is there a way to use this formula and have it
combine duplicate values?

[jazzista]

MIke: What would be the solution if you have repeats in the  products: I tried the formula with repeat products and I am getting the sum of the 1st occurrence on each product, Any suggestions? Also, on the conditional formatting, are you doing an array inside the conditional format menu? I am trying the AND function but I cannot get it to work  Thanks

[brianx]

thanks for posting this, even though i don't know i can use it. i use excel and vlookup often. hope to see more! I love this channel, but i need to start somewhere to catch up this series. any suggestion? Thanks!

[MalinaC]

Oooo, yes, it's crazy AND amazing :)

[BillSzysz]

This is crazy thing, isn't it? :-))

[DomingosCJM]

Hi Mike, I was using the function INDEX() and noticed that if I use it as =INDEX(TableName;0;1) it returns a value while it is on the same side of the table, even on another sheet. For me this is very strange and I don't quite like it, do you know any reason for this or have you noticed this strange behaviour?