Excel Magic Trick 440: Array Formulas Advanced Tips

[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?

[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]

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!

[aidir09]

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

[ExcelIsFun]

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

[krn14242]

Wow, extreme video on arrays and array formulas.. I needed an array countif, but now see I can use sumproduct without making it an array (Ctrl,Shift,Enter). thanks Mike.

[ExcelIsFun]

You are welcome!

[smumin160]

Where is workbook 440-451?? This is very helpful. Thank you in Advance.

[ExcelIsFun]

Oh man! I forgot to post this workbook! I will post it tomorrow morning!

[ExcelIsFun]

Just posted it! It should be there now.

[smumin160]

Many thanks :)

[ExcelIsFun]

You are welcome!