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.

{ 9 comments… read them below or add one }

Sir,

I am trying to make a formula to return null value.

The requirement is to get null value if all the cells in the relevant range is null.

Regards,

Abraham

You could try using nested IF statements or concatenate all of the cells in your range and do one IF test for null.

> Nested IF Statements:

=IF(a1=””,if(b1=””,”NULL”,”NOT NULL”),”NOT NULL”)

> Concatenate then Check:

=IF(concatenate(a1,b1)=””,”NULL”,”NOT NULL”)

These formulas haven’t been tested, but I think they’ll solve the problem you’re having.

The ISNA function can also be used as an alternative to the ISERROR. You may need to be made aware of a legitimate error which ISERROR would hide from you. The ISNA function can be used in the same was as described above.

Thanks. That’s a good tip.

My formulas never have errors so I haven’t used this before. jk 😉

can i use two different rows and two differend colum in one time vlookup

i wanted to

tow different lookup_value,

tow table_array,

two col_index_num,

range_lookup

in one time use in formulas please help me

Nilesh,

There’s no way to natively query two separate arrays or lookup values in a VLookup function. However, depending on how your spreadsheet is set up, you might be able to use the Concatenate function to combine values. Your situation sounds very specific, so there could be any number of different solutions to your problem. With creative use of the concatenate function, I think you have a good shot at solving this problem.

Hi,

I am trying to use VLOOKUP and having a problem when i have a “+” or “-” sign in my lookup value. For example I have a grade criteria and the Grade is A+ when I am running VLOOKUP on it its not returning me the value.

Can you please help?

Thanks.

Ali

Hi, i have problem with vlookup.

When I use vlookup formula I recived always same result (21). I need more results (21,25,etc).

Ascending or descending of data is not possible (this is small part of very big table)

Can you help me?

Thanks, Igor

Sample:

1 11

2 21

3 31

4 41

5 51 look for 2

1 12

2 25 result 21 21

2 23 21 25

3 32 21 23

4 42 21 20

5 52 21 25

1 13

2 20 wrong correct

3 33

4 43

5 53

1 14

2 25

3 34

4 44

5 54

I’m having thi ssame issue as well with a similar senario.

Someone please reply.

{ 2 trackbacks }