Looking Up a Customer Account Number

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 tab le 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 5.4 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.

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.

Add to Technorati Favorites


// Related Posted - GOOGLE!

Loading



Related Websites
No comments yet.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>