Comparing Old List and New List
Have you ever wonder in what way you can find out the difference in between a new list and old list? Often my student came running to me saying they get confuse where should the VLOOKUP be ?
Don’t Panic! First you must know what you want. If you get a very clear picture of what you want, then your problem should be solved. Anyways, whenever the topic of comparing the new list with old list is when you want to find out which are the new products, which are the old products. Or you could be searching which is the new customer and which is the old customer.
If that’s what you want? Simple. If you want to identify the new product or new customer, then the VLOOKUP should be placed in the new list. Use the customer in the new list to crawl and find a match from the old list. If the VLOOKUP return #NA, which means this particular product or customer is not found in the old list, which also mean that’s the new item.
Don’t get it? Check this out.
Finding new Product
Below I have prepared 2 lists. The list on the left is the Product list from year 2010 while the one on the right side is the Product list from 2011.
I have also created an extra column in the Product list from 2011 called “Who is new?”. That’s where the VLOOKUP is going to be.
As always, before the VLOOKUP, I’m going to turn the first Product table as a Table object. I hardly skip this step whenever I’m creating any formula because it makes it so easy while referencing and maintaining it.
Then we can begin the craw now!.
The Lookup Value of the VLOOKUP will be the Product from the List 2011, while the Table Array will be the Product2010 table. Then the Column Index Number will be 1, since the only column I have from the Product2010 table is the Product column, and finally finish it up with a FALSE for the Range Lookup.
This is how the VLOOKUP should look like
=VLOOKUP(C3, Product2010 ,1 ,False)
If you realize the end results turn out some are product name some are #NA. When #NA is being returned, that means the product is not found in the Old list, which makes it the new Product.
Finding the discontinue product
What about now it is the other way round, you are looking for the discontinue product. Where should the VLOOKUP be? The Old list or New list?
Aha! If you’re think the VLOOKUP should be in the Old list, you’re right-on! I’m going to construct the VLOOKUP in the table Product2010. Use the product from the old list to crawl and search whether there is a match from the table Product2011 (which is the new table). If #NA is being returned, this means the product is no longer exist = discontinued.
But first! Let me create the table.
After the table has been created, now we can start building the VLOOKUP in the old list.
The Lookup Value of the VLOOKUP will be the Product from the List 2010, while the Table Array will be the Product2011 table. Then the Column Index Number will be 1, since the only column I have from the Product2010 table is the Product column, and finally finish it up with a FALSE for the Range Lookup.
This is how the VLOOKUP should look like
=VLOOKUP(A3, Product2011 ,1 ,False)
If the #NA is being returned is means the product is not found in the new list = discontinued.
ERROR free report
If you find VLOOKUP returning #NA is annoying, you can nest the VLOOKUP function within the IFERROR function. So, with the IFERROR, it will help you to convert the #NA into something more meaningful. It is mostly use to trap and handle errors.
The syntax of IFERROR:
=IFERROR(Value, Value_If_Error)
So, the Value will be where the VLOOKUP is, while the Value_If_Error will be the text where you would like to display.
