Learn the power of Lookup Functions in Excel
Excel spreadsheets can become large and overwhelming at times. To manage large spreadsheets and find data easily, use the =VLOOKUP function as described in this tutorial.
Many people I've worked with over the years work have large spreadsheets containing hundreds and thousands of rows and columns worth of data. Often users manually search these hundreds or thousands of rows and columns to find specific data over and over again. This can be so time-consuming and has the potential for lots of errors.
But, when you need to find information in a large spreadsheet, there is an easier way. The =VLOOKUP function works quickly and easily.
=VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone's name, in order to find out what you don't know, like their phone number.
Watch Video here or Continue to Read On...
To use the =VLOOKUP function, follow these steps.
For this example, use the Excel table in the image above. We've setup a table with the first column containing names of employees. The next few columns contain test scores pertaining to licensing information needed for the Human Resources department.
The Human Resources Director holds this data in an Excel spreadsheet and is asked to provide Dan's score on Test3.
The formula may be setup in a separate cell and edited to find specific information when needed. In our example, it would be very easy for the HR Director to simply find Dan's name in the "EMPLOYEE" column and look at the score for "Test 3." However, the =VLOOKUP function is really helpful when the spreadsheet contains hundreds or more rows and columns of data. So, pretend the data is so large that it's not obvious what Dan's score is on Test 3.
In Cell H2 we'll place the following formula:
=VLOOKUP("Dan",A2:F18,4,true)
Here is an explanation of each of the fields within the parentheses of the formula.
"Dan" is the Lookup_Value. This is the data you are telling Excel to search.
A2:F18 is the Table_Array. This is the range containing the data you want to search.
4 is the "Col_index_num" field. This is the number of the column that contains the data you need. In our example, "Test 3" scores are located in Column D, which is the 4th column in the spreadsheet.
True is the "Range_lookup" field. This field defines how close a match should exist between the "Lookup_value" (Dan) and the value in the column on the lookup table. If Dan's name is listed as "Daniel," you may want to leave this field blank. When placing "false" in the "Range_lookup" field, Excel looks for an exact match to the "Lookup_value."
NOTE: If Range_lookup is either "true" or is omitted, the values in the first column of Table_array must be placed in ascending sort order; otherwise, =VLOOKUP might not return the correct value.
So what just happened? I told Excel, “Here is a value in the left-hand column of my data - this is the name of the person I want to look up. Now look through this range of cells, and in the fourth column to the right (Test 3 data), find the value on the same row.”
The bottom line is that Excel spreadsheets containing large amounts of data do not need to be overwhelming and do not need to take a long time to find specific data. Use the =VLOOKUP function and your large Excel spreadsheets will become easy to manage.
Here's another example.
What if I wanted to keep the =VLOOKUP formula, but continue to use it to find results for different people based on what name I enter?
To do that, I'll simply need to replace the Lookup-Value reference. Instead of specifically entering "Dan" to find Dan, enter the field where you can enter a different name each time you'd like to search.
So, now the formula becomes =VLOOKUP(H1,A2:F18,4,True).
Now if we enter "Billy" into cell H1, the result provides us Billy's score of 88.
If we then change H1 by entering "Dan," we now see Dan's Test 3 score of 78 again.
Now, practice on your own. What if you wanted to change the Test # you want to search for?
The examples here are so powerful, but as you can now imagine, =VLOOKUP can be so powerful and helpful. I urge you to try this for a basic search of data you have in a spreadsheet and as you get more and more comfortable with =VLOOKUP, try to push it like we started in the second example here.
Good Luck and Happy Excelling!
If you're interested in learning more about managing data in Excel, sign up to receive a 50% discount on my course: Excel Managing and Auditing Data.
