Excel Annoyances

LOOKUP FUNCTION ANNOYANCES

LOOK UP VALUES IN DATA LISTS

The Annoyance:

I know Excel isn’t a database...that’s Access. And I know I shouldn’t expect miracles, but I’m hoping you can help me look up a value in a table. For example, I have a list of sales reps, sorted by their employee ID number (as in Figure 4-8). If I see a transaction report with an employee ID, isn’t there some way I can look up who belongs to that number without using the Find function?

Figure 4-9. You can find corresponding values in a data list.

The Fix:

Sure, you can do it. The process involves some pretty fancy footwork with advanced Excel functions, but once you grasp what’s going on you’ll be fine.

The first function you can use to find a value in a worksheet is the LOOKUP() function. Basically, the LOOKUP() function identifies a row in your worksheet by looking in, say, column A for a value you specify. Once it identifies the row that contains that value in column A, it looks in, say, column C of that same row, snatches the value it finds there, and “returns” it, or displays it, in whatever cell holds your formula.

The LOOKUP() function has this syntax:

    =LOOKUP(lookup_value, lookup_vector, result_vector)

whereby:

• lookup_value is the cell (or value) to find in the table. It could be an employee’s ID number, a Social Security number, or another unique identifier.

• lookup_vector is the range to search for the ...