Computer Training - Excel VLOOKUP Tutorial
Excels Formula To Lookup Data in Columns– Tips and Tricks
Jul 29, 2009
The VLOOKUP formula in MS Excel can be used to look up a table or array, and then return useful data. The function is easy to use, and needs only four items to be entered. The example used here gives a step-by-step explanation, as well as showing how to fix some of the problems often found with VLOOKUP.
Excel VLOOKUP – Example
The VLOOKUP formula in Excel is described here using an example. A customer wishes to calculate monthly mortgage payments. To do this, the customer needs to know the interest rate to use, but the interest rate depends what percentage of the mortgage the customer will deposit. (See Figure 1, columns A to G). Four possible options are available, and the interest rate for each needs to be looked up from columns I to J.
Excel VLOOKUP Formula - Set Up
The two most important values in the VLOOKUP function are the item to be looked up, (the percentage deposit) and the value to be returned (the interest rate). Before trying to enter the VLOOKUP formula, the data table to be used (columns I and J in Figure 1) must be set up by the user. The conditions needed for VLOOKUP to work are:
- The value to be looked up (the percentage deposit) needs to be in the leftmost column of the VLOOKUP area.
- The lookup area needs to be sorted in ascending order of the leftmost column. (percentage deposit).
- The value for percentage deposit to be looked up in columns D and I need to be compatible. For example, if column D is text, then so must column I.
Excel VLOOKUP Formula – Tips and Suggestions
- If a large number of cells have been filled in using VLOOKUP, the Excel workbook will appear to be very slow. If this happens, it is often useful to copy the results from VLOOKUP and use Edit-Paste Special-Values to speed up processing.
- If the value to be looked up (column I in the example) is calculated, it may not match an exact field. For example, an entry of 15% in column D may not match column I exactly, if column I = 10% + 5%. This is due to errors within Excel.
- After the VLOOKUP formula has been applied, it is important that the user does not sort or delete the lookup area (columns I and J).
Using the Excel VLOOKUP Formula
Figure 2 shows what values need to be entered into the Excel VLOOKUP formula. There are four entries to make, and these are described using the example shown in cell F2:
- Value to be looked up - this can be a literal such as “10”, cell contents such as D2, or a function like average(A1, 0.02).
- Table to look up – this can be somewhere on the same sheet, or in a separate sheet. The easiest way to fill this in is to use the locating drop-down icon (see Figure 2) to highlight the area where the data columns are (I and J in this example). If the formula will be copied down to other cells then the user should input the format $I$1:$J$7 or I:J
- Which value to return – this is the column number of the data to be returned. It must be to right of item used to search. In the example shown, the second column (interest rate) is needed, so 2 is entered in this section.
- Exact match required - Is an exact match needed or will the closest do? “FALSE” means an exact match is needed. For example, in Figure 2, cell F5 contains “#N/A” because there is no interest rate for the deposit level of 25.5%. If this section were changed to “TRUE” then the nearest deposit level would be 25%, and the interest rate would change from “#N/A” to 0.03 (or 3%).
Excel VLOOKUP Formula Summary
The Excel VLOOKUP function is extremely useful when trying to look up data. There are a few pitfalls that may arise to avoid, but with just a little practice these can be avoided. More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
 |
 |
