VLOOKUP - Compare Customers in 2 Sheets
Posted by Mike M on June 12, 2001 5:12 PM
I am a novice so hopefully you can help me.
I get a weekly spreadsheet which has about 8 pages of customers each week. It is very inefficient to try and figure out what has been added or deleted. Someone suggested that I use vlookup, but the directions in the Excel don't seem to be that intuitive.
Let's say one spreadsheet is called June1.xls and the new one is June8.xls. The Customer names are contained in column C. Here is where it gets fuzzy. How do I compare the Customer names in the new spreadsheet versus the older spreadsheet to show where the customer is new? This should be simple but so I am. Please help. Thanks.
Posted by Ben O. on June 12, 2001 5:47 PM
Okay, Mike, here's how you do it. In your top data row on June1.xls, in any column, enter =VLOOKUP( and press the equals (=) button to start the equation wizard. Here are the boxes you'll have to fill in:
Lookup Value: This is the customer name you'll be searching for. It'll probably be C2 for your first row.
Table_Array: This is where the formula will look for the lookup value you specified in the previous step. Click on your June8.xls spreadsheet and select the column containing the customer names. You can select more columns to the right of the names, but it will only search in the leftmost column.
Column Index Number: This is the column from your table array that the formula will return the value from if it finds a match. The leftmost column will be 1 (even if your table array starts in column C). If your table array is only one column wide, just enter 1.
Range Lookup: Enter either FALSE or TRUE here. If you enter TRUE, the formula will accept less-than-exact matches. I almost always set this to FALSE.
Your equation will look something like this:
=VLOOKUP(C2,[June8.xls]Sheet1!$C$2:$D$44,1,FALSE)
Use the autofill feature to fill the formula into every row that contains a customer name on your June1.xls spreadsheet.
The way I set up this formula, it will return the customer's name if it finds a match. So if you have Joe Smith in C2 and it finds a match, the formula will return Joe Smith. You can make the formula return something like "New Customer," but that requires a more complicated formula:
=IF(ISNA(VLOOKUP(C2,[June8.xls]Sheet1!$C$2:$D$44,1,FALSE)),"New Customer","")
Good luck,
-Ben
I am a novice so hopefully you can help me.
