### Excel 2010 Magic Trick 670: AGGREGATE Function Array Formula #4 Extract Records 1 criterion

See how to use the AGGREGATE function to create a formula for extracting records with one criterion. This is a lookup problem where you want to return multiple records from one lookup value. This formula is not an array formula because it does not require Ctrl + Shift + Enter. Use AGRREGATE to Replace SMALL function in array formula. Array formulas: 1) For AGGREGATE function, Numbers come first, then divide, then criteria; 2) For IF function, Criteria come first, then comma, then numbers. AGGREGATE function joins the ranks of LOOKUP, INDEX and SUMPRODUCT as functions that can handle arrays! Download files: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm

Length:
08:32