Consolidating spreadsheets in excel
The VLOOKUP formula has four parts: Make sure that you don’t make the most common mistakes as having a too small range (search column as well as return column must be within the search range) and start counting the search column as column number 1.
The primary function of SUMIFS is to sum up values matching your criteria.
VLOOKUP and SUMIFS are rather popular whereas INDEX/MATCH is usually not that well known.
In a pinch, it’s possible to copy and paste the various cells that are required, placing them all in the same sheet.When you’ve arranged your data to those specifications, create a new worksheet.It’s possible to run the consolidation procedure in a sheet where there’s already data, but it’s easier not to.In our case, we must make sure that each criteria combination only exists once in our table.In the example on the right hand side, we would have exactly this problem: There are 3 VW Golfs in our table, so the return value is the sum of the 3 prices.