Excel VLookup Function

A wonderfully powerful and easy to use way of comparing data across Excel worksheets is the VLookup function. I’ve been helping a lot of people with this function lately, so I thought I’d get a quick tutorial out there.

Let’s start with the syntax of the VLookup function:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Here’s an example of how you would use VLookup to find rows on the “OtherSheet” worksheet that match something in the first row of your main worksheet:

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

Super. So what does this all mean?

lookup_value: The cell you want to match to the other sheet.
table_array: The range where your lookup_value exists in the first column.
col_index_num: The column of table_array that you want to show if you have a match. (First column is 1.)
range_lookup: Optional. TRUE will find matches “similar” to your lookup_value, and FALSE will only find an exact match.

As an example, you have a list of states with their populations on Sheet1. On Sheet2, you have a list of states with the number of lakes they have. Sheet1 lists all states but some are missing from Sheet2.

Option 1: Manually match the states together and copy the values over. 1 by 1.

Option 2: Use a VLookup function. In column C of Sheet1, you would use the vlookup function to see if the value in column A of that row (lookup_value) is found anywhere in column A of Sheet2 (table_array). Also included in table_array is the column that lists the number of lakes. (If there were 10 states listed, the range might be A1:B10.)

Warning!: When typing the range in your VLookup function, remember to put the dollar sign in front of the row numbers. Otherwise, as you copy the function down Sheet 1, the range will move down with you. So, when you get to row 10, rather than having the range of A1:B10, the range will be A10:B19. Use the dollar sign ($) and Excel knows you want the numbers to remain static.

Advanced VLookup Hack:
Whenever your VLookup doesn’t find a match, it insists on putting #N/A in the cell. Needless to say, if you’re making a report for someone, you don’t want all of these ugly values throughout your sheet. You could sort your data and delete all of the #N/A values manually, but you’d also be deleting the VLookup formulas. Therefore, if your data changes you might miss values that you otherwise would have matched.

To get rid of the #N/A values automatically, use this formula:

=IF(ISERROR(VLOOKUP(…)), “Val if not found”, VLOOKUP(…))
For Example:
=IF(ISERROR(VLOOKUP(C13,’State-List’!C$2:E$100, 3, FALSE)),“”,VLOOKUP(C13,’State-List’!C$2:E$100, 3, FALSE))

In the above example, we check to see if the VLOOKUP results in an error. (ie, value not found)
So if the “IF” does result in an error, we put the “Val if not found” value in the cell.
(Generally, you’d simply use back-to-back quotes to enter a null value into the cell.)
Otherwise, we run the VLOOKUP as originally intended.
(Replace the dots with the actual VLOOKUP parameters)

You don’t even need to understand this formula to use it. Simply type it exactly as shown above, except, insert your own VLookup function in both places where it says VLOOKUP(…) and remember that whatever you type within those quotation marks will show up if there is no match for your VLookup. I generally recommend back to back quotes for a null value, or a zero if you’re working with numbers.

Well I hope that helps clarify what Excel’s VLookup function is all about. If you found this post helpful, please consider subscribing to my RSS feed. That way you won’t have to remember to come back when I post another great Excel tip.

If you still have questions, please leave a comment. I’d be happy to provide additional clarification if needed.