VLOOKUP

VLOOOKUP is definitely a guru function. Here it is...

VLOOKUP means vertical lookup. It's a database type function, and allows you to combine data from two different sources. The sources can be different lists on the same worksheet, different lists in the same workbook, or two different files all together. The choice is yours. For the most part, the process is the same.

Here's a scenario. Let's say you have a list that contains your classmates, their seat number, and their eye color. You have another list that contains your classmates' phone numbers. You want to combine the lists, but you've never heard of VLOOKUP. So, you try to sort them and then copy and paste the phone numbers manually. When you try this, you find out that your phone number list is shorter than your classmates information list. You start looking through trying to find the holes, but then your realize your list is 150 lines long. This will never work! VLOOKUP to the rescue.

You will use the VLOOKUP to find matching values between the two spreadsheets by looking VERTICALLY up and down the second sheet. Once it finds a match, you will instruct it to look to the value on the 2nd column over (in this case) and return it to the first spreadsheet. Life has never been easier.

Here's what the formula will loook like:
=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)
ANATOMY of this formula
A2: Class mate name.
[Phone.xls]Sheet1!: the second spreadsheet that contains the phone numbers
$A$2:$B$312: the "table array." This contains classmate name and their phone number
0: This just means FALSE. Your choices are 1 or 0 or TRUE or FALSE. Choose FALSE to have the VLOOKUP do an exact match. I've never found any value in choosing 1 or TRUE. (CAREFUL! If you leave this blank, it defaults to 1 or TRUE, and you will probably not get the results you expect.)


This is called a formula bar. Click on the FX to bring up a wizard to build your VLOOKUP.


The lookup value will be your classmates' name. Or, if you doing some other type of activity, it is the value that is common between your two lists.

The table array is the 2nd list that has the data that you want to look up. This needs to include the whole list that may contain matching data. So, it goes from the very first name and phone number to the very last name and phone number.

The Col_Index_Num (Column Index Number) references the column that has the value you want it return to the first list. In this case, it's 2. But, it can be any value from 1 to 256 (but, gosh, that would get complicated!)

The last value in the box, for my money, is always 0. I don't know what to do with results that are a close, but not exact, match.

Now, drag that formula down for the whole list. You've saved yourself a lot of work!

That's it. I hope it's easier for you than I made it sound. I recommend practicing. And, if you ever find yourself sorting and manually matching things up, STOP! Use VLOOKUP. IT WORKS. IT WORKS EVERY TIME (although sometimes you have to massage it a bit.)

A few tips and things to remember:
If both lists are in the same workbook, the wizard will not make your table array absolute with the dollar signs. Make sure that you use dollar signs. Otherwise, when you drag down the formula, you will be changing your table array each time you drag it down.

VLOOKUP will always return the first value it find in the second list. If the second list contains two entries for a classmate with two different phone numbers, it will only return the first phone number it finds in the list. If you sorted the list differently, it would return the other number.

If there is no matching value, it will return #N/A.

If you're getting an error and you know there are matches between the two lists, try and change the formatting on both lists so that the lookups are formatted exactly the same.

And, if that still doesn't work, imagine me standing over your shoulder. That's always done the trick for folks asking for Excel advice.

Good Luck!