Use the Lookup Function in Excel

Whenever you keep track of anything with spreadsheets, there'll come a time when you want to find information without having to scroll through a list. That's when the LOOKUP function can be useful.

Let's say you have a simple list of 1000 clients with three columns: last name, phone number, and age. If you want to find the phone number for Monique Wikihow, you can look at every name in that column till you find it. To speed things up, you can sort the names alphabetically, but if you have many clients with last names starting with "w", you might still get a headache browsing the list. Using the LOOKUP function, however, you can simply type in the name and the spreadsheet will spit out Miss wikiHow's phone number and age. Sounds handy, doesn't it?

Steps

  1. Create a two column list toward the bottom of the page. In this example, one column has numbers and the other has random words.
  2. Decide on cell that you would like the user to select from, this is where a drop down list will be.
  3. Once you click on the cell, the border should darken, select the DATA tab on the tool bar, then select VALIDATION.
  4. A pop up should appear, in the ALLOW list pick LIST.
  5. Now to pick your source, in other words your first column, select the button with the red arrow.
  6. Select the first column of your list and press enter and click OK when the data validation window appears, now you should see a box with an arrow on, if you click on it your list should drop down.
  7. Select another box where you want the other information to show up.
  8. Once you clicked that box, go to the INSERT tab and FUNCTION.
  9. Once the box pops up, select LOOKUP & REFERENCE from the category list.
  10. Find LOOKUP in the list and double-click it, another box should appear click OK.
  11. For the lookup_value select the cell with the drop down list.
  12. For the Lookup_vector select the first column of your list.
  13. For the Result_vector select the second column.
  14. Now whenever you pick something from the drop down list the info should automatically change.

Tips

  • Make sure when you are in the DATA VALIDATION window (Step 5) the box labeled IN-CELL DROPDOWN is checked
  • Whenever you are completed you can change the font color to white, to make the list hidden.
  • Save your work constantly, especially if the list is extensive
  • if instead you would like to type in what you want to search for you can skip to step 7

Related Articles