Lookup Functions

 

VLOOKUP Functions

The VLOOKUP Function might be the most talked about Excel function. Job interviewers ask candidates to explain how it works. It is the function that weeds out Excel wannabes. If you want to become competent in Excel you must master this function.

VLOOKUPs allow you to “lookup” a value in a data set and return information from the row where that value 

is found. Here is an example:

This will look up the “Salary” of Emp ID = 4. We will walk through the syntax, including notes from this example..

VLOOKUP Syntax

:

• lookup_value - The value that you want to look up. We hard-coded this as “4” in the previous example.
• table_array - The table array where the lookup will take place. The leftmost column is the “lookup column” where Excel will look to match the lookup_value. In our example, the VLOOKUP is searching for “4” in the column with Emp IDs.
• col_index_num - The column number of the value that you want to output. Include the “lookup column” (the leftmost column) when counting columns. In our example, “Salary” is the 5th column.
• [range_lookup] - **ImportantTRUE or FALSE. FALSE will return only exact matches. TRUE will return the closest match that is less than or equal to the lookup_value when searching through sorted numerical data. Notice that we used FALSE to find an exact match.

1. Use a VLOOKUP to calculate the salary of employee #4

(Hint)

Save Question to Custom Practice Set?

2. Use a VLOOKUP to calculate the birthdate of employee #2

(Hint)

Save Question to Custom Practice Set?

As we mentioned earlier, it is bad practice to hard-code values into formulas. You might be tempted to hard-code the 'col_index_num' into the VLOOKUP Function, but try to avoid it. When looking up tables of data, try placing the 'col_index_num' in the row above the table and reference those cells in your VLOOKUP:

3. Create a table of VLOOKUPs by creating a formula in cell G4 that can be copied to the rest of the table. Hint: You will need to lock cell-references.

(Hint)

Save Question to Custom Practice Set?

So far we have only searched for exact matches with the FALSE argument. This is the most common use of the VLOOKUP. Now we will look at inexact matches with the TRUE argument.

4. The temperature for tomorrow is 92. Look up the number of heat strokes expected.

(Hint)

Save Question to Custom Practice Set?

HLOOKUP Function

VLOOKUP stands for “Vertical” lookup. Alternatively, you can do a “Horizontal” lookup with the HLOOKUP Function. The HLOOKUP works exactly the same as the VLOOKUP, except it searches for the lookup_value in the top row instead of the left-most column.

5. Lookup the percentage of Total US Population in Texas.

(Hint)

Save Question to Custom Practice Set?

Lookups & Wildcards

Remember Wildcards? The ? Wildcard represents any single character and the * Wildcard represents any number of characters. You can use Wildcards with lookup functions to perform inexact matches

6. Using a string containing a ? Wildcard, create a VLOOKUP formula to lookup the Hire Date for the Employee with the incomplete Emp ID.

(Hint)

Save Question to Custom Practice Set?

Index and Match Functions

The VLOOKUP is the most commonly used lookup function, but it has one big problem: When using a VLOOKUP, your lookup column must be the leftmost column on the data. If your data doesn't come in this format you would need to manipulate the data before you can perform a VLOOKUP

Instead, you can use the INDEX and MATCH functions together. When using INDEX / MATCH, the order of your data set does not matter.

MATCH Function

The MATCH function looks up a given value in an array, returning that value's position in the array. You can use the MATCH Function on a range one row tall or one column wide. It has three inputs:

• lookup_value - The value that you want to look up.
• lookup_array - Where you want to search for the value. Note: it must be one row tall or one column wide.
• [match_type] - The type of match. Use 0 for exact (most common. Similar to using FALSE with a VLOOKUP). 1 = less than or -1 = greater than. Note: when using 1 or -1, the data must be sorted in the proper direction otherwise the MATCH Function will generate an error.

7. Use the MATCH function to see what number corresponds to the letter F in the alphabet

(Hint)

Save Question to Custom Practice Set?

You can see that the MATCH function only returns a number corresponding to the value's position in the lookup_array. To return an actual value (like when using a VLOOKUP), you will need to pair the MATCH function with the INDEX Function.

INDEX Function

The INDEX function returns a value found at a specific position (determined by row and column references) in an array. Inputs:

• array - An array of cells
• row_num - The row number of the array. Note: can be omitted in a single-row array.
• column_num - The column number of the array.

Here is an example where the INDEX Function is used to perform multiplication by looking up the two numbers in a multiplication table.

Hopefully, you can see how the INDEX and MATCH Functions work together. The MATCH Function returns a position in an array, which can be used as an input to the INDEX Function.

8. Use the INDEX function to calculate the number of HRs for Mike Wright. Note: we already used MATCH Functions to calculate the row and column numbers.

(Hint)