Did you know that you can populate an entire data table using only on VLOOKUP formula?
This can be done using VLOOKUP in an array formula. You benefit by saving loads of time which leads to efficiency and a quicker turn around of the work. Other benefits of Array formulas includes improved formula integrity and quicker processing time as you are using a single formula.
Lets say you want to retrieve the account details for the months of April,May and June, instead of trying to figure out which cell references should be absolute or relative you simply select the cells which you would like to populate with the single formula, enter your formula and there you go.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2007, 2010 and 2013
1. Select the Monthly Income Statement worksheet.
2. Select the range C6:E6.
3. While this range is selected, enter the formula: =VLOOKUP(A6,Datalist,{4,5,6},0) in the formula bar and press CTRL + SHIFT + ENTER to return the results. (The values for April,May,June will be displayed).
- A6 is the lookup value
- Datalist is the named range for the source data on the Transactions worksheet. (Either type in Datalist or press F3 and select Datalist from the dialogue box.
- {4,5,6} refers to the column index numbers for the months of April,May,June on the transactions worksheet.
- 0 implies that an exact match will be found
4. Copy the formula down (in the white cells only)
Sales
- Highlight the data range C6:E6
- Copy down the entire range/selection to C10:E10
Cost of Sales
- Highlight the data range C6:E6
- Copy and paste to C12:E12
- Copy down the entire range/selection to C16:E16
Direct Expenses
- Highlight the data range C12:E12
- Copy and paste to C18:E18
- Copy down the entire range/selection to C20:E20
Other Income
- Highlight the data range C18:E18
- Copy and paste to C23:E23
Expenses
- Highlight the data range C23:E23
- Copy and paste to C25:E25
- Copy down the entire range/selection to C29:E29
Instead of looking up the values for April, May and June separately the VLOOKUP array formula ensures that the data for the three months is retrieved in a single formula. This leads to time saving as tasks will be done quickly and efficiently.
