Ms Office Tune Up

The discount schedule lookup table in the previous section is an example of a range lookup where you check to see if the lookup value falls within a range of values, such as 1-5 or 6-25. However, you often come across situations in which you need to match specific values instead of ranges. For example, you might need to look up an employee ID, a part code, or a book’s ISBN number. These are examples of discrete value lookups where your formula needs to match some value exactly.

A table of customer names and account numbers is a good example of a lookup table that contains discrete lookup values. In this case, you want to use the VLOOKUP() function (or, less likely, the HLOOKUP() function) to find an exact match for a customer name you specify, and then return the corresponding account number. Figure 1 shows a simple data-entry screen that automatically adds a customer account number in column E after the user selects the customer name from a drop-down list in column D.

Figure 1

A simple data-entry worksheet that uses the exact-match version of VLOOKUP()to look up a customer’s account number based on the selected account name.

An example of a function that accomplishes this is in cell E3:

=VLOOKUP(D3, A2:B93, 2, FALSE)

The value in D3 is looked up in list of customer names in column A, and because the range_lookup argument is set to FALSE, VLOOKUP() searches for an exact match. If it finds one, it returns the corresponding account number from column B.

TIP

If you start with the formula in cell E4 and fill it down column E, you’ll get #N/Aerrors in all the new columns. VLOOKUP()generates this error if no match is found in the lookup range, which will be the case while the value in column D is blank. It is better to have Excel display the null string (”") instead.To do that, use the IFERROR()function to test whether the lookup will fail, as shown here:

=IFERROR(VLOOKUP(D3, A2:B93, 2, FALSE), “”)

The IFERROR()function first calculates the VLOOKUP()result. If the result isn’t an error, IFERROR()displays the result normally; if the result is an error, IFERROR()displays the null string.

Popularity: 2% [?]