In our previous tip, we shared an Income and Expenditure template for you to download and customize as needed. Now we’ll show you how you can quickly look up and retrieve data from the transactions worksheet to the Income and Expenditure Statement worksheet using the VLOOKUP and MATCH functions.
Identifying the column index number in the VLOOKUP function can be tedious and time wasting, especially If you are looking up data from a large worksheet. This is where the MATCH function (which returns the relative column or row position of a value in a given range) comes in, as it can be used with the VLOOKUP function to make it easier and quicker to identify the column number. This then leads to a quick look up and retrieval of data.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2007, 2010 and 2013
1. To define named ranges.
a. Select the transactions worksheet.
b. Highlight the data range A1:I20.
c. Enter Account in the name box and press Enter.
d. Highlight the data range A1:I1.
e. Enter “Headings” in the name box and press Enter.
2. To enter the Vlookup and Match function.
a. Select cell C6 on the monthly income statement worksheet.
b. Select the Formulas tab.
c. Select Lookup and Reference.
3. Then select Vlookup from the bottom of the list.
4. Enter as per screen shot below.
5. For the Table_array value, press F3 and select Account from the list of defined names.
6. When you reach the second argument in the match function (Col_index_num argument), press F3 and select Headings from the list.
Notes:
- A6 is the lookup value and represents the first Account Code
- Account is the defined name for the data array A1:I20 on the transactions worksheet
- We use the match function to retrieve the column number for the data to be retrieved
- $A$1 is the lookup value and represents the name of the month
- Headings is the defined named for the data range A1:I20 on the transactions worksheet
- The relative position for June with the range A1:I20 on the transactions worksheet is 6.
- Hence the column number is 6
- We enter the value 0 because we want the exact match
- 0 under range lookup represents the exact match for the Vlookup function.
7. Select OK.
8. Copy the formula down only in the white cells.
You did not have to identify the column index number as it was returned automatically. Besides retrieving data quickly, using the Vlookup and match functions helps in selecting the correct fields from the data list.
