Excel Magic Trick 440: Array Formulas Advanced Tips

We will try to answer the questions:
1.What is the Difference Between Array and Range?
2.What is an Array Formula?
3.When Do I Have To Use Ctrl + Shift + Enter?

Advanced Excel Array Formula Topics

Category:

Science & Technology

Tags:

• Excel
• What
• is
• an
• Array
• Formula?
• and
• Range
• When
• Do
• Have
• To
• Use
• Ctrl
• Shift
• Enter
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[chor1962]

Hi Mike

Thanks for this video it clearly explains the difference between range and arrays, i have another question here, how did the lookup function work when you put in the look up value of 100 but the look up vector had only one value of 5, it still gives the result of 2.

[ExcelIsFun]

Thanks for the 5 stars!

What minute mark are you refering to?

[ExcelIsFun]

That formula is explained in these videos:

Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)

Excel Magic Trick 309: Partial Text Match: Why 2^15?

LOOKUP, VLOOKUP and MATCH all can do "Aproximate match" lookup. In this case when you give it a lookup number that is bigger than all the numbers in the array, it returns the last value. Since lookup = 100 and the 5 is the only numner, it chooses that one.

Watch the above videos for full explination.

[chor1962]

Thanks for the explanation Mike will go thro these videos.

Chor

[ExcelIsFun]

You are welcome!

[manducerva]

Mike!

You passion for this is outstanding!

Thanks a lot!!!

[ExcelIsFun]

You are welcome!

[chor1962]

it would be around 17.56 Mike , the lookup value is 100 and the lookup vector shows a value of 5

Thanks again

Chor

[ExcelIsFun]

Yes, knowing a lot about array formulas, gives you real power in Excel!

[aidir09]

wow... very usefull... this vid help me alot.. thx