Sponsored Link

There was an error in this gadget

Sponsored Link

Sunday, March 8, 2009

Vlookup function to find if value is present in range

I am little known for my MS Excel knowledge which I thought of sharing around. For beginners, Vlookup is biggest challenge and personally I have ample of people who ask me to teach Vlookup. Here is post to help all beginners with Vlookup formula, my all best wishes are with you. Approach will be in following manner. I will first explain you the formula syntax followed by example. To make best out of this post, I would suggest you to copy-paste the example in a MS Excel sheet. 

Formula: VLOOKUP(Valueyouwanttofind, Rangefromwhichyouwantofind, Columnnumber, SortedorUnsorted)

Valuesyouwanttofind are the customer id listed in column 1 of table below.

CustomerID CustomerExistorNot Result
50043 =VLOOKUP(A2,Sheet2!$A$1:$C$20,1,0) 50043
50048 =VLOOKUP(A3,Sheet2!$A$1:$C$20,1,0) 50048
40325 =VLOOKUP(A4,Sheet2!$A$1:$C$20,1,0) #N/A

 Rangefromwhichyouwantofind are the customersid listed in column1 of table below. Since we are working to find out if the values existst or not hence we will choose columnnumber as 1 which will return you the customerid from table below and will return #N/A if the customer do not exist in table 2.

CustomerID CustomerName City
50041 Sharma K Mumbai
50047 Chauhan M Delhi
50048 Ritu K Kanpur
40023 Khan L Vashi
45601 Verma K Belapur
50023 Areeb Mumbra
40215 Riyaz A Mumbai
41280 Dadan A Kharghar
40326 Amit D Belapur
50023 Faheem A Mumbra
40215 Riyaz A Mumbai
40351 Rodriquez P Mumbai
42892 Raheem A Kolkata
50043 Keith S Mumbai
40052 Mahadevan S Mumbai
40234 Ganguly D Mumbai
40021 Smith M Delhi
40020 Chakorborthy M Vashi
40019 Raju C Delhi

Copy-Paste both table in Sheet1 and Sheet2 starting from A1 cell.

SortedorUnsorted is the last parameter which can be true/false or 1/0. I would suggest you to use 0 if you are not sure whether the data is sorted(indexed) or not.

The most important things you should remember when you use Vlookup is Rangefromwhichyouwantofind should have Valuesyouwanttofind values listed as the left most columns. Like in the above scenario, you may have columns before customerid column but your range for Rangefromwhichyouwantofind should start with customerid. Also, use absolute reference in Rangefromwhichyouwantofind.

Do write us your view about this post. We will provide you something better that would help you understand better.  Enclosed below is link from Official Microsoft site explaining Vlookup function.

http://support.microsoft.com/kb/181213

Also, take a look at video tutorial design to help you with Vlookup.

 

If you are new/first time visitor, do subscribe via email to receive latest updates, e-books, free add-ins and lot more.

We assure you knowledge, not SPAM!

1 comments:

sswcharlie said...

vlookup only extracts the contents of a cell. I have a comments box in a cell that I also want extracted to the new cell. Can I use vlookup or do I need a different function(s) to do this.

Post a Comment