Free Online Tutorials on Spreadsheets

Things to remember about VLOOKUP

In the previous lesson, we learned the basics of the VLOOKUP function. In this section, we will make you familiar with the important characteristics of the VLOOKUP.

Absolute references increases usability

You may need to look up for more than one value. For this, you will first need to change table_array relative reference into absolute reference.

For example, In case if you do not change the reference of table_array then it will change relatively into A5:E9.

Without absolute reference

To make the table_array absolute reference, execute following steps:

1-Go to the B13 and press F2.

2-From the appeared formula select A4:E8 and press F4.

[Note: To learn about absolute, relative and mixed references, Click here.]

3-Now when you copy and paste the VLOOKUP below, the table_array will remain the same.

With absolute reference


VLOOKUP is case-insensitive. For example, in the screenshot below, the VLOOKUP function returns the #N/A error.

Exact Match/ Approximate Match

[range_lookup] argument in the syntax has two available options:
TRUE = approximate match (default). FALSE = exact match. When you select TRUE, Excel will search for an approximate match like 3182 or 3182.22. Otherwise, it will result in #N/A error if FALSE is selected.

Right Lookup

VLOOKUP function always looks up a value in the leftmost column and returns the corresponding value from the selected column in the right. In case, we input lookup value from the right side, Excel returns the #N/A error.

Copyright © 2016 - 2019 | All Rights Reserved