Consolidating information in excel

16 Feb

One of the most often used functions when creating an Excel model is consolidating data from different sources.There are 3 major formulas for combining data from different tables or worksheets: VLOOKUP, SUMIFS and INDEX/MATCH.Survey respondents said they spent approximately 12 hours each month "consolidating, modifying and correcting the spreadsheets they collaborate on with others and reuse frequently," which Kugel said equals about one and a half business days. Kugel said the majority of time users spend with spreadsheets isn't for doing complex work -- they're simply trying to find and correct errors, another weakness of the tool."Thoroughly checking spreadsheets for errors is a chore that no one wants to do, so errors slip past unnoticed," Kugel said.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 is probably the best known formula for getting data from another table.

This problem occurs because spreadsheets aren't bound to a single, unified source, he said."Sometimes the differences between spreadsheets are not all that important, or you can work around them, but in many cases it's important not to waste time trying to resolve whose spreadsheet is right, because they could all be wrong," Kugel said.According to Ventana research, nearly half of enterprise-sized companies have experienced this problem -- 44% of survey respondents said they grapple with multiple, inconsistent spreadsheets.If you return value is text, both VLOOKUP and the INDEX-MATCH combination work.The following decisions are then no exclusion criterions but rather matters of convenience.