Lookup & Reference Functions

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-
row range and returns the corresponding value from another one-column or one-row range.
LOOKUP's default behavior makes it useful for solving certain problems in Excel.

Download Dataset➡️Click Here

Vlookup
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table
and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range
$E$4:$G$7 and returns the value in the same row from the third column (third argument is
set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not
found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays
the same, while the relative reference (A2) changes to A3, A4, A5, etc.

Detailed Information on VLOOKUP

Download Dataset➡️Click Here

The VLOOKUP function is one of the most popular functions in Excel. This contains many
easy-to-follow VLOOKUP examples.

Exact Match
Most of the time you are looking for an exact match when you use the VLOOKUP function in
Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost
column of the red table (second argument).
2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same
row from the fourth column of the red table.

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact
match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a
#N/A error.

3. Here's another example. Instead of returning the salary, the VLOOKUP function below
returns the last name (third argument is set to 3) of ID 79.
Approximate Match
Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth
argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost
column of the red table (second argument). There's just one problem. There's no value of 85
in the first column.

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an
approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it
will return the largest value smaller than 85. In this example, this will be the value of 80.
3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same
row from the second column of the red table.

Note: always sort the leftmost column of the red table in ascending order if you use the
VLOOKUP function in approximate match mode (fourth argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns
the corresponding value from a column to the right.

1. For example, the VLOOKUP function below looks up the first name and returns the last
name.
2. If you change the column index number (third argument) to 3, the VLOOKUP function looks
up the first name and returns the salary.

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID.
The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in
Excel to perform a left lookup.

First Match
If the leftmost column of the table contains duplicates, the VLOOKUP function matches the
first instance. For example, take a look at the VLOOKUP function below.
Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Partial Match
The VLOOKUP function supports wildcards for partial matches. For example, take a look at
the VLOOKUP function below.

Explanation: an asterisk (*) matches zero or more characters. The VLOOKUP function matches
the first instance that begins with "Jess". Use "*"&G2 for an "ends with" match. Use
"*"&G2&"*" for a "contains" match.

Vlookup is Case-insensitive
The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the
VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.
Explanation: The VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or
miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance).
Use INDEX, MATCH, and EXACT in Excel to perform a case-sensitive lookup.

Multiple Criteria
Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to
perform a two-column lookup.

Note: the array formula above looks up the salary of James Clark, not James Smith, not James
Anderson.

#N/A error
If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost
column.

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

Note: the IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel
2007, combine IF and ISNA or use IFERROR.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can
use the IF function to check whether a condition is met, and return one lookup table if TRUE
and another lookup table if FALSE.

1. Create two named ranges: Table 1 and Table 2.

2. Select cell E4 and enter the VLOOKUP function shown below.

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second
argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1,
if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP
function to TRUE to return an approximate match.
3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges,
we can easily copy this VLOOKUP function to the other cells without worrying about cell
references.

Hlookup
In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Note: if you have Excel 365 or Excel 2021, you can also use XLOOKUP to perform a horizontal
lookup.
Xlookup
If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The XLOOKUP
function is easier to use and has some additional advantages.

INDEX and MATCH

Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use
INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH.

Download Dataset➡️Click Here

Match
The MATCH function returns the position of a value in a given range.

Explanation: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set
this argument to 0 to return the position of the value that is exactly equal to lookup_value
(A2) or a #N/A error if not found. Use INDEX and MATCH in Excel and impress your boss.
Index
The INDEX function below returns a specific value in a two-dimensional range.

Explanation: 92 was found at the intersection of row 3 and column 2 in the range E4:F7.

The INDEX function below returns a specific value in a one-dimensional range.

Explanation: 97 found at position 3 in the range E4:E7. Use INDEX and MATCH in Excel and
impress your boss.

Index and Match

Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll
need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you
one of the many other powerful formulas Excel has to offer.
Two-way Lookup
The INDEX function can also return a specific value in a two-dimensional range. For example,
use INDEX and MATCH in Excel to perform a two-way lookup.

Two-column Lookup
Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to
perform a two-column lookup.
Note: the array formula above looks up the salary of James Clark, not James Smith, not James
Anderson.

Closest Match
To find the closest match to a target value in a data column, use INDEX, MATCH, ABS, and
MIN in Excel.

Choose
The CHOOSE function returns a value from a list of values, based on a position number.
Explanation: The boat found at position 3.

Table Magic
Tables can make VLOOKUP formulas a lot easier. In the example below, a single VLOOKUP
function automatically looks up all country codes.

“Artificial Intelligence systems can predict

hypoglycemic events in diabetics three hours in
advance.”