Whenever you store anything in a spreadsheet, the time will come that you will want to find information without having to scroll through a list. This is when the SEARCH function can be useful. Take, for example, a simple list of 1000 customers with three columns: name, phone number and age. If you want to find Monique Wikihow's phone number, you can look at each name in the name column until you find it. To speed things up, you can sort the names alphabetically, but if you have a lot of customers with surnames starting with "W", you may still have a headache looking in the list. Using the SEARCH function, however, you can simply type in the name and the spreadsheet will return information about Miss Wikihow's age and phone number. Sounds simple, doesn't it?
Steps
Step 1. Create a list with two columns towards the bottom of the page
In this example, one column has numbers and the other has random words.
Step 2. Decide which cell you would like to select from, this is where there will be a drop down list
Step 3. Once you click on the cell, the borders should turn dark, then select the DATA tab on the toolbar, and choose Validate data
Step 4. In the pop-up window that will appear, choose List from the criteria provided in Allow
Step 5. Now to choose your source, in other words, the first column, press the button with the red arrow
Step 6. Select the first column of the list, press enter and click OK when the data validation window pops up, now a small box with an arrow should appear next to the cell, if you click the arrow the list should appear contained in the reference cells
Step 7. Choose another cell where you want the other information to appear
Step 8. Once you have clicked on this cell, go to the INSERT tab and choose FUNCTION
Step 9. Once the window opens, choose SEARCH and REFERENCE from the list of categories
Step 10. Find the SEARCH function in the list and double click on it, another window should appear on which click OK
Step 11. In the Value parameter, indicate the cell containing the drop-down list set up in the previous steps
Step 12. For the Vector parameter select the first column of the list
Step 13. For the Result parameter, select the second column of the list, then click OK
Step 14. Now whenever you choose something from the drop down list in the first cell, the information of the second cell should change automatically
Advice
- Make sure that when you are in the DATA VALIDATION window (step 5) the List option in the cell is checked.
- Once you have completed the formulas you can change the color of the characters in the cells of the blank list, so that they are not seen.
- Save your work constantly, especially if the list is large.
- Instead of choosing from the drop-down list, you can also type the value (number, name, or other) you want to search for.
