You learned how to use the INDEX, MATCH, and INDIRECT functions in this lesson to enhance the normal lookup functionality in Excel, and you’ll see how those functions improve upon VLOOKUP and HLOOKUP. I add additional lookup functionality to the customer due diligence file, and then got to practice with a case study wher compare the financial performance of several REITs and have to work with “messy” data to do so. In this project, I used several Excel Lookup function & Data Tables :
- Combining INDIRECT with &
- HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEX Functions
- Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET & INDEX
- The MATCH Function
- Combining H/VLOOKUP, OFFSET, INDEX, CHOOSE with MATCH
- Creating Drop Down Menus with Data Validation
- Combining INDIRECT with MATCH
- Using COLUMN and ROW Functions as Counters in Complex Formulas
- Data Tables
Customer Sales Data Analysis (CSV document)
Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a ID of a Sales Rep by the part Sale- Reps column.
=SUMIFS(‘Orders& Summary’!$J$3:$J$1001,’Orders& Summary’!$M$3:$M$1001,”=”&’2020-Monthly’!$B4,’Orders& Summary’!$K$3:$K$1001,”>=”&’2020-Monthly’!F$2,’Orders& Summary’!$K$3:$K$1001,”<“&’2020-Monthly’!G$2)
The challenge here will be that the data we need is spread across many different worksheets. And to make it even more fun, the columns are in different orders. The geographies, the cities here they’re not all the same, they’re different between each different dataset.
In order to incorporate the year, I used using INDIRECT and INDEX and MATCH to figure monthly sales figure. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Monthly Sales =INDEX(INDIRECT(“‘”&TEXT(Rep_Year,”yyyy”)&”-Monthly’!$B$3:$N$12″),MATCH(Rep_ID,INDIRECT(“‘”&TEXT(Rep_Year,”yyyy”)&”-Monthly’!$B$3:$B$12″),0),MATCH(Rep_Month,INDIRECT(“‘”&TEXT(Rep_Year,”yyyy”)&”-Monthly’!$B$3:$N$3″),0))
Used COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of Sales Rep ID in a customer list.
Sales Rep =COUNTIF(M4:M1004,U4)
Y columns = Sales Rep
Used IF function to find out each Sales Representative’s work loading: