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:
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