|
|
Featured Tutorial
Featured Macro
Excel Magic Trick 465: Lookup Entire Row (ALL COLUMNS) with INDEX for Cake Manufacturing Calculation
Cake Difficulty Score Calculations for manufacturing company. Learn three important tricks for SUMPROCDUCT and INDEX functions:
1.If you put a zero into the column_num argument in INDEX, it will return "all the columns" or an entire row.
2.If you put a zero into the row_num argument in INDEX, it will return "all the columns" or an entire column.
3.SUMPRODUCT can only handle array arguments arrays with the same dimensions when you use a comma. To overcome this limitation, use multiplication instead of commas.
Also see a VLOOKUP array formula (not as efficient as the SUMPRODUCT INDEX formula but interesting nonetheless).
Related videos:
Mr Excel & excelisfun Trick 39: Lookup Row (ALL COLUMNS) w VLOOKUP & OFFSET wrapped in SUMPRODUCT
Excel Magic Trick 465: Lookup Entire Row (ALL COLUMNS) with INDEX for Cake Manufacturing Calculation
VLOOKUP All Columns - 1161 Dueling Excel Podcast
1.If you put a zero into the column_num argument in INDEX, it will return "all the columns" or an entire row.
2.If you put a zero into the row_num argument in INDEX, it will return "all the columns" or an entire column.
3.SUMPRODUCT can only handle array arguments arrays with the same dimensions when you use a comma. To overcome this limitation, use multiplication instead of commas.
Also see a VLOOKUP array formula (not as efficient as the SUMPRODUCT INDEX formula but interesting nonetheless).
Related videos:
Mr Excel & excelisfun Trick 39: Lookup Row (ALL COLUMNS) w VLOOKUP & OFFSET wrapped in SUMPRODUCT
Excel Magic Trick 465: Lookup Entire Row (ALL COLUMNS) with INDEX for Cake Manufacturing Calculation
VLOOKUP All Columns - 1161 Dueling Excel Podcast