The reason that Excel is such a powerful application, and therefore so popular, is because of the tools like functions and formulas that you can harness within the application itself. This power can be attributed to the fact that the application contains the power of Visual Basic for Applications (or VBA, for short.) VBA is a simple programming language included within the Office suite of applications that allow users of the applications the ability to add programming code to their applications. This ability allows the user to add almost unlimited functionality to their documents and spreadsheets.
The following tutorial will show you how to harness the power of the VBA function “LOOKUP” within your Excel spreadsheets. For a solid grounding in Excel to be able to understand the concepts within this tutorial, I would suggest a course like the Excel Course (Basic and Advanced) course available from udemy. This course will teach the basic concepts of working with cells and formulas and will help you to follow the more advanced concepts that are included later in this tutorial.
Using the simple LOOKUP function to find student names
For the purposes of the tutorial I have used a spreadsheet that contains data from a class of students. My initial spreadsheet contains the names and student numbers of the students in the class. The initial spreadsheet looks like this:
We will use the simple version of the LOOKUP function to find the name of the student when we type in the student number.
How the LOOKUP function works
The LOOKUP function allows you to search a column of data for a specific value within Excel to return the corresponding value within another row. LOOKUP can be used to find values within rows or columns of data.
Excel automatically determines whether it should return a value from a row or column depending on the data within the spreadsheet. Where the spreadsheet contains more rows than columns, Excel will search the column and then return the corresponding row value. Take a look at the example below:
Our data contains ten rows and two columns, so based on the above spreadsheet Excel will search the column for data and then return the value from the corresponding row.
Now take a look at the same data below, but instead of columns, the data has been represented in rows:
Here we have two rows and eleven columns so in this case Excel will automatically search the rows for the value and then return the value from the corresponding column.
The LOOKUP function works in the same way that we would search for data as humans. If we had the above spreadsheet in front of us and someone gave us a student number and asked us to find the name of the student, we would scan through the values of the student number column or row and once we’d found that number we would use the row or column to determine the student’s name.
Let us take a look at how we can achieve this in Excel using the LOOKUP function. Functions in Excel can be initiated by typing in the function itself or using the Function menu within Excel. For more on how to use functions, Excel for beginners will teach you more about how to use the basic functions within Excel.
For this tutorial we will first use the LOOKUP function using the Function menu. To create a LOOKUP function that will allow us to type in the student number and have Excel find the name for us, we will create the function in the Student name cell that we have created at the top of our worksheet. To access the LOOKUP function, click the Formulas menu tab and then choose LOOKUP from the drop down list called “Lookup & Reference”.
The Function argument window opens and that allows us to enter the values for our LOOKUP function. The LOOKUP value in our function will be based on what the user types into the Student no: cell at C3. So we will enter, or click C3. This will tell the LOOKUP function what value to look for in our column.
The next value we need to enter is the LOOKUP vector. This is the column or row that Excel must scan to find the value we are searching for. So we want to tell Excel to scan column B from row 6 to 10 to see if it can locate the value we need.
Finally the result vector is used to specify the return value if Excel finds the value. The results we want Excel to return in this case is the student name so the result vector in the data is contained in column C 6 to 10.
Here is what the function will look like once the criteria for the search have been entered:
Now if the teacher types in a student number in the student no. cell at C2, the Student name field will use the LOOKUP function to find the corresponding student’s name as shown in the two worksheet examples below:
Let us add a few more columns of data to our students’ spreadsheet to compile a report for each student using the LOOKUP function to fill in the information for each student. Take a look at the spreadsheet below:
Instead of using the formula bar to enter the values, we will type the formula directly into the cell this time. We want the result to appear in cell C6 to reflect the biology grade of our student so we start by typing an equal sign into cell C6. Then we type “LOOKUP”. When we type the function name, Excel automatically displays the syntax of the function for us so that we can use that syntax guide to create or specify the criteria for our function.
Once again we want to use the value typed in at cell C2 to use as the value that Excel will look for in the data. We want Excel to search for the student number within the student no. column which is B16 to B25. And we want Excel to return the value in column D if it finds the student number.
We will use the same formula to add the other results, using the same search value and search column but using different results columns for each subject. We are also going to add an average grade to the worksheet using the SUM function and then use an IF statement to determine if the student has passed or failed. For more advanced training on Excel, you can invest in a course like Mastering MS Excel available from udemy.
Our final spreadsheet will look like this:
Things to note about the LOOKUP function
There are a few things you need to take note of when working with the LOOKUP function. First, the data in the search column must be sorted in numerical or alphabetical order. If the data is not sorted then Excel will return the wrong answer. Take a look at the spreadsheet below:
We have told Excel to search for the student with student number 8. The right answer is George but because the data is not sorted in numerical order, Excel returns the value “Mike”. So it is important when using the LOOKUP function within Excel, to ensure the data you are searching is sorted in ascending order before you search the data.
Another thing to note about the LOOKUP function is that it will look for a value equal to or greater than the value you choose as a search value. So for example if we type in 12, the result of our search would be as follows:
Twelve is greater than 10 so Excel returns the values for the columns that correspond with student number 10. This is also incorrect. This is where the VLOOKUP and HLOOKUP functions can help us out.
The VLOOKUP and HLOOKUP functions in Excel
The VLOOKUP and HLOOKUP functions are similar to the LOOKUP function in that they use the same basis for searching for data based on criteria set by the user. The VLOOKUP and HLOOKUP functions differ from the LOOKUP function because they allow you to use additional criteria for the search.
Instead of using the LOOKUP function in our worksheet to find the student name and grades, let us take a look at the VLOOKUP function and how it can help us to avoid the errors we pointed out above.
This is the Function argument window which allows you to specify the arguments for the VLOOKUP Function. First we need to tell Excel what to search for. You can enter a value or a cell address into this argument. In our case we will enter the cell address C2 – which is the student number our user types in to retrieve the values we need.
Next we specify the table that Excel needs to search to find the data. This differs a little from LOOKUP because in the LOOKUP function we specified the column that contained the search value as opposed to the whole table.
Now that Excel knows what to search for and where to search, we need to tell Excel which column of the table contains the value we are looking for should Excel find the value we have specified. In this case we are searching for the value in column 2 which contains the student names.
Finally, we can tell Excel if it must find an exact match or if it can find the closest match for us. This is criteria that will help us eliminate the error of searching for student number twelve as we saw above. By typing in FALSE, Excel will only return a value if it finds an exact match for the search term.
So now if we search for 12, our spreadsheet will show the following:
Using the LOOKUP function with IFERROR and IF statements
LOOKUP, HLOOKUP and VLOOKUP can be used in conjunction with other Excel formulas. Let us write a statement that handles the #N/A error so that the results look a little better. To handle the error, we can use the IFERROR statement to output the statement “Student does not exist” if the VLOOKUP function cannot find the student number.
Here is the Syntax of the function using the IFERROR statement along with the VLOOKUP function:
This statement tells Excel to look for the student number VLOOKUP function. It looks for the student number within the table array we specify. It returns the value of the student name from column number two for that student number, but if it doesn’t find the student number then it must return the value of “Student does not exist”.
If we add an IF statement to assign grades to our students instead of marks, then we could use a nested IF function to assign different grades to our students using the VLOOKUP function to find each grade for that student. It looks a little intimidating but take a look at the function below:
The result of our above formula is as follows:
The Average grade also uses the VLOOKUP function to add up the various grades to create an average grade. The function looks like this:
If you want to learn how to use IF statements and other advanced Excel statements, then you could invest in a course that would teach you to use Excel like a Pro. Fast.
The LOOKUP function in Excel can save you a lot of time and trouble when you need to search for data within your spreadsheets. Courses that will teach you how to harness the power of VBA within your Excel and (and other Office applications like Word) are well worth your time, because they will help make your life far easier in the long run.