Excel Forum
Similar Content
Reverse Row or Column Order in a Worksheet
Macro: This macro reverses the order of a selected row or column in excel. It will completely rev...
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
Macro: This Excel macro allows you to filter a data set on multiple columns and criteria at once....
Find the Last Column with Data in Excel VBA
Tutorial: How to find the last column in a row that has data. This includes selecting that column or...



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





Got a Question? Ask it Here in the Forum.