How To Use Vlookup

What is a Vlookup?

Vlookup is one of the most popular and powerful formulas in Excel. You can use it find certain values within a large dataset and act when those items are found. To master the Vlookup, you should first learn it’s syntax.

What does a Vlookup consist of?

Here is a sample vlookup formula.

=VLOOKUP(A1,Sheet1!$A:$B,2,FALSE)

Let’s examine what’s going on in this formula.

Your first parameter immediately follows the open parenthesis and specifies the location of the data you are searching for. In our example, our quarry resides in cell A1.

=VLOOKUP(A1,Sheet1!$A:$B,2,FALSE)

Next, following the comma, we are telling Excel where we want to look for the data that resides in A1. In our case, we think the data might be hiding on Sheet1 within columns A and B.

=VLOOKUP(A1,Sheet1!$A:$B,2,FALSE)

The dollar sign we prepend to the column letter means we want the entirety of the column searched. Excel will look from 1 to infinity, or more accurately 1 to 1,048,576 as that is the maximum number of rows the application will support.

Immediately after we let Excel know where to look, we are going to specify where the data resides that we would like to output if a match is found. For example, if we want the string "Super Easy CRM" to populate anytime a match is found we would need to place it in the second row of the range specified in the 2nd parameter of the Vlookup. You can choose any number you’d like if it doesn’t exceed the range you have specified. So, if your lookup range is from A to C, you could not list the number 4 as the location of the data you want to output since there are only 3 positions available between A and C.

=VLOOKUP(A1,Sheet1!$A:$B,2,FALSE)

The final parameter specifies whether you want an exact match or a partial match. If you want an exact match select FALSE, and if you want the opposite select TRUE.

=VLOOKUP(A1,Sheet1!$A:$B,2,FALSE

Troubleshooting your formula

My Vlookup is not working

What does a N/A# mean?

If you get an N/A# it means one of two things: either your Vlookup returned no results for the data you were looking for or your data does not match anything listed in the reference data (2nd parameter).

Common reasons that data does not match are:

1. There is errant whitespace either before or after the values in the data. The Vlookup will be looking for an exact match in most cases so if the two datasets you are working with are not formatted the same, you won’t get the results you are looking for.
2. The data types are not the same. When Excel iterates through the dataset(s) you are specifying in the Vlookup, it is trying to match not only the values but the value types as well. For example, the numeric value 954 when stored as text is not same as 954 stored as a number.

What does SPILL# mean?

This error just tells you that you did not specify a value to lookup in your formulas. Most likely you failed to list the first parameter.

More Articles...