Excel Lookup and Reference Functions and Formulas

 613-749-4695 [email protected]

# Excel Chapter 19 of 24: Excel Lookup and Reference Functions and Formulas

Excel offers you 18 functions in the logical  category. Here are the ones (4) that you will use more often.

 Functions What it Does INDEX Uses an index to choose a value from a reference or array MATCH Looks up values in a reference or array HLOOKUP Looks in the top row of an array and returns the value of the indicated cell LOOKUP Looks up values in a vector or array VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell INDIRECT Returns a reference indicated by a text value OFFSET Returns a reference offset from a given reference
 The most important functions in this category INDEX, MATCH INDEX/MATCH formulas are so important that they are the subject of an  entire workbook with step by step development instructions and  examples. See "excel-tutorial-index-match.xls" . You will also find  applications in "excel-template-invoice.xls" where you enter the name of the client or select it in a drop down list and the address appears automatically in another cell, enter the product number and its description, unit and cost appear automatically. See also how to use INDEX/MATCH to develop scenarios and "   what if" applications in business models "excel-example-scenario.xls". You will also discover how to use INDEX/MATCH to develop cascading drop-down lists where you select a country in a first drop-down list and only cities from this country appear in the second drop-down list. See "excel-tutorial-ddlists.xls". INDEX/MATCH is also used to create relational databases in Excel. Suppose that the details about your clients are in one database and the sales are  in another and you want to analyse sales by cities. Because the client's number is in both databases (sales and clients), you can analyze sales by city using an INDEX/MATCH formula to bring the cities into the sales database. The LOOKUP Group The functions in this group are widely known among  advanced users. But once they discover the more powerful and less limited INDEX/MATCH they are kind of pushed aside. Click on the links below to access the pages of this website describing how they work and what is their limits. Excel Lesson 19A - Excel HLOOKUP Function Excel Lesson 19B - Excel LOOKUP Function Excel Lesson 19C - Excel VLOOKUP Function Other Functions When you start developing more complex business models or when you want to calculate and chart moving averages and moving " Year to Date" you will need the two following functions. INDIRECTIf in cell A1 of Sheet1 you have this value (Sheet2!A1) and in cell A2 of Sheet1 you have the following formula:=INDIRECT(A1) the result will be the value of  cell A1 of Sheet2. OFFSETThe most intellectually challenging function in Excel. The general format of this function goes as follows:=SUM(OFFSET(D1,1,1,3,3))In plain English...sum the range of 3 rows by three columns that starts 1 row below and one column to the right of D1 (the anchor). So if you have 2  in all 9 cells E2 to G4 the result will be 18. See an example with detailed explanations and instructions in the workbook "excel-example-dynamic-report.xls"