986

Excel Magic Trick 986 One Lookup Value, Extract Multiple Items, Display Horizontally (A Closer Look)

• Report rights infringement
• published: 15 Jan 2013
• views: 41772

Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm One Lookup Value, Extract Multiple Items, Display Horizontally: 1. From given name list all groups that have selected that name horizontally. 2. Which means extract items with one condition. 3. Display items horizontally: use COLUMNS function for number incrementor. 4. "Data extraction" array formula 5. Problem: one lookup value, "Name", that shows up as multiple duplicates in a column. 6. Therefore you have many "matched" row numbers that the lookup function has to deal with. 7. Because we have an array of "matched" row numbers means we must use an array formula. 8. Lookup function will be: INDEX. 9. Function that can "select" correct "relative position" for INDEX function as we copy formula down: SMALL or AGGREGATE. 10. IF function or Boolean array operation will help us with our one condition "Name". 11. AGGREGATE is only in Excel 2010 or later. AGGREGATE can do array calcualtion in array argumnet for functions 14 to 19 without having to enter the formula with Ctrl + Shift + Enter. 12. IF with logical test is better to use than IFERROR because the IF will not run the entire array formula lookup function for cells that should return a null text string. IFERROR will run the entire array formula in every cell. This matters for formula calculation time. Key Concepts: 1. One lookup value 2. Multiple "matches" in a column. 3. Multiple "matches" causes problems for standard lookup functions. 4. Multiple "matches" means multiple "Realative Positions" 5. We must then create an array of these "Realative Positions" in our formula. 6. This requirement that we create an array of relative positions makes this an array formula. 7. Then as we copy formula we must choose, one by one, the relative positions.