When working on a worksheet, there will be times when you want to find information without having to scroll through long lists. That's when you can take advantage of the LOOKUP function. Say you have a list of 1,000 clients with three columns: Last name, phone number, and age. To find the wikiHow Monique phone number, for example, you can look up the name in a column of a worksheet. You can sort the names alphabetically to make it faster. What if it turns out that many clients whose last names start with "w"? It takes extra precision and also a long time to look for it on the list. However, with the LOOKUP function you can simply type a name, and the worksheet will display the phone number and age for the person. Sounds useful, right?
Step
Step 1. Make a two-column list at the bottom of the page
In this example, one column has numbers and the other column has random words.
Step 2. Specify the cell as where the user will select
This is the cell that will display the drop-down list.
Step 3. Once the cell is clicked, the cell border will darken
Select the DATA tab on the toolbar, then select VALIDATION.
Step 4. A small window will appear, select LIST from the ALLOW list
Step 5. To select the source of the list, in other words your first column, select the button with the red arrow
Step 6. Select the first column from the list then press enter and click OK when the data validation window appears
You will now see a box with arrows, which when clicked will bring up a list.
Step 7. Select another box where you want more information to appear
Step 8. Once you have clicked on the box, go to the INSERT and FUNCTION tabs
Step 9. Once the box appears, select LOOKUP & REFERENCE from the list of categories
Step 10. Look for LOOKUP in the list and then double click it
Another box will appear. Click OK.
Step 11. For lookup_value select the cell with the drop down list
Step 12. For lookup_vector select the first column from your list
Step 13. For result_vector select the second column
Step 14. From here, whenever you retrieve something from the drop-down list, the information will be automatically changed
Tips
- Make sure when you are in the DATA VALIDATION window (Step 5) the box with the words IN-CELL DROPDOWN is checked.
- When you're done, you can change the font color to white to hide the list.
- Save work periodically, especially if your list is long.
- If you want to type in the keywords you want to search for, you can go directly to step 7.