How-To Geek
Using VLOOKUP in Excel
VLOOKUP is one of Excel’s most useful functions, and it’s also one of the least understood. In this article, we demystify VLOOKUP by way of a real-life example. We’ll create a usable Invoice Template for a fictitious company.
So what is VLOOKUP? Well, of course it’s an Excel function. This article will assume that the reader already has a passing understanding of Excel functions, and can use basic functions such as SUM, AVERAGE, and TODAY. In its most common usage, VLOOKUP is a database function, meaning that it works with database tables – or more simply, lists of things in an Excel worksheet. What sort of things? Well, any sort of thing. You may have a worksheet that contains a list of employees, or products, or customers, or CDs in your CD collection, or stars in the night sky. It doesn’t really matter.
Here’s an example of a list, or database. In this case it’s a list of products that our fictitious company sells:
Usually lists like this have some sort of unique identifier for each item in the list. In this case, the unique identifier is in the “Item Code” column. Note: For the VLOOKUP function to work with a database/list, that list must have a column containing the unique identifier (or “key”, or “ID”), and that column must be the first column in the table. Our sample database above satisfies this criterion.
The hardest part of using VLOOKUP is understanding exactly what it’s for. So let’s see if we can get that clear first:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Put another way, if you put the VLOOKUP function into a cell and pass it one of the unique identifiers from your database, it will return you one of the pieces of information associated with that unique identifier. In the example above, you would pass VLOOKUP an item code, and it would return to you either the corresponding item’s description, its price, or its availability (its “In stock” quantity). Which of these pieces of information will it pass you back? Well, you get to decide this when you’re creating the formula.
If all you need is one piece of information from the database, it would be a lot of trouble to go to to construct a formula with a VLOOKUP function in it. Typically you would use this sort of functionality in a reusable spreadsheet, such as a template. Each time someone enters a valid item code, the system would retrieve all the necessary information about the corresponding item.
Let’s create an example of this: An Invoice Template that we can reuse over and over in our fictitious company.
First we start Excel…
…and we create ourselves a blank invoice:
This is how it’s going to work: The person using the invoice template will fill in a series of item codes in column “A”, and the system will retrieve each item’s description and price, which will be used to calculate the line total for each item (assuming we enter a valid quantity).
For the purposes of keeping this example simple, we will locate the product database on a separate sheet in the same workbook:
In reality, it’s more likely that the product database would be located in a separate workbook. It makes little difference to the VLOOKUP function, which doesn’t really care if the database is located on the same sheet, a different sheet, or a completely different workbook.
In order to test the VLOOKUP formula we’re about to write, we first enter a valid item code into cell A11:
Next, we move the active cell to the cell in which we want information retrieved from the database by VLOOKUP to be stored. Interestingly, this is the step that most people get wrong. To explain further: We are about to create a VLOOKUP formula that will retrieve the description that corresponds to the item code in cell A11. Where do we want this description put when we get it? In cell B11, of course. So that’s where we write the VLOOKUP formula – in cell B11.
Select cell B11:
We need to locate the list of all available functions that Excel has to offer, so that we can choose VLOOKUP and get some assistance in completing the formula. This is found by first clicking the Formulas tab, and then clicking Insert Function:
A box appears that allows us to select any of the functions available in Excel. To find the one we’re looking for, we could type a search term like “lookup” (because the function we’re interested in is a lookup function). The system would return us a list of all lookup-related functions in Excel. VLOOKUP is the second one in the list. Select it an click OK…
The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in order to complete the VLOOKUP function. You can think of this box as the function is asking us the following questions:
- What unique identifier are you looking up in the database?
- Where is the database?
- Which piece of information from the database, associated with the unique identifier, do you wish to have retrieved for you?
The first three arguments are shown in bold, indicating that they are mandatory arguments (the VLOOKUP function is incomplete without them and will not return a valid value). The fourth argument is not bold, meaning that it’s optional:
We will complete the arguments in order, top to bottom.
The first argument we need to complete is the Lookup_value argument. The function needs us to tell it where to find the unique identifier (the item code in this case) that it should be retuning the description of. We must select the item code we entered earlier (in A11).
Click on the selector icon to the right of the first argument:
Then click once on the cell containing the item code (A11), and press Enter:
The value of “A11” is inserted into the first argument.
Now we need to enter a value for the Table_array argument. In other words, we need to tell VLOOKUP where to find the database/list. Click on the selector icon next to the second argument:
Now locate the database/list and select the entire list – not including the header line. The database is located on a separate worksheet, so we first click on that worksheet tab:
Next we select the entire database, not including the header line:
…and press Enter. The range of cells that represents the database (in this case “’Product Database’!A2:D7”) is entered automatically for us into the second argument.
Now we need to enter the third argument, Col_index_num. We use this argument to specify to VLOOKUP which piece of information from the database, associate with our item code in A11, we wish to have returned to us. In this particular example, we wish to have the item’s description returned to us. If you look on the database worksheet, you’ll notice that the “Description” column is the second column in the database. This means that we must enter a value of “2” into the Col_index_num box:
It is important to note that that we are not entering a “2” here because the “Description” column is in the B column on that worksheet. If the database happened to start in column K of the worksheet, we would still enter a “2” in this field.
Finally, we need to decide whether to enter a value into the final VLOOKUP argument, Range_lookup. This argument requires either a true or false value, or it should be left blank. When using VLOOKUP with databases (as is true 90% of the time), then the way to decide what to put in this argument can be thought of as follows:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
As the first column of our database is not sorted, we enter false into this argument:
That’s it! We’ve entered all the information required for VLOOKUP to return the value we need. Click the OK button and notice that the description corresponding to item code “R99245” has been correctly entered into cell B11:
The formula that was created for us looks like this:
If we enter a different item code into cell A11, we will begin to see the power of the VLOOKUP function: The description cell changes to match the new item code:
We can perform a similar set of steps to get the item’s price returned into cell E11. Note that the new formula must be created in cell E11. The result will look like this:
…and the formula will look like this:
Note that the only difference between the two formulae is the third argument (Col_index_num) has changed from a “2” to a “3” (because we want data retrieved from the 3rd column in the database).
If we decided to buy 2 of these items, we would enter a “2” into cell D11. We would then enter a simple formula into cell F11 to get the line total:
=D11*E11
…which looks like this…
Completing the Invoice Template
We’ve learned a lot about VLOOKUP so far. In fact, we’ve learned all we’re going to learn in this article. It’s important to note that VLOOKUP can be used in other circumstances besides databases. This is less common, and may be covered in future How-To Geek articles.
Our invoice template is not yet complete. In order to complete it, we would do the following:
- We would remove the sample item code from cell A11 and the “2” from cell D11. This will cause our newly created VLOOKUP formulae to display error messages:
We can remedy this by judicious use of Excel’s IF() and ISBLANK() functions. We change our formula from this…=VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)
…to this…
=IF(ISBLANK(A11),”",VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
- We would copy the formulas in cells B11, E11 and F11 down to the remainder of the item rows of the invoice. Note that if we do this, the resulting formulas will no longer correctly refer to the database table. We could fix this by changing the cell references for the database to absolute cell references. Alternatively – and even better – we could create a range name for the entire product database (such as “Products”), and use this range name instead of the cell references. The formula would change from this…
=IF(ISBLANK(A11),”",VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
…to this…
=IF(ISBLANK(A11),”",VLOOKUP(A11,Products,2,FALSE))
…and then copy the formulas down to the rest of the invoice item rows.
- We would probably “lock” the cells that contain our formulae (or rather unlock the other cells), and then protect the worksheet, in order to ensure that our carefully constructed formulae are not accidentally overwritten when someone comes to fill in the invoice.
- We would save the file as a template, so that it could be reused by everyone in our company
If we were feeling really clever, we would create a database of all our customers in another worksheet, and then use the customer ID entered in cell F5 to automatically fill in the customer’s name and address in cells B6, B7 and B8.
If you would like to practice with VLOOKUP, or simply see our resulting Invoice Template, it can be downloaded from here.
|
Subscribe |
Daily Email Updates |
|
You can get our how-to articles in your inbox each day for free. Just enter your email below: |
- By Mark Virtue on 03/30/10
Comments (75)
-
ctBeautifully explained! I just saw a job posting that specified the applicant must know vlookup. After sifting through confusing postings elsewhere, I was happy to find yours. Can’t wait to try this out — even if it doesn’t land me the job. :)
-
Sally
Fantastic! The best explanation I’ve come across.
-
Karen
This is the best explanation of VLOOKUPS I’ve ever found. This solves so many problems for me. Thank you!
-
Yolanda
Your explanation of vlookups is the only clear explanation I’ve come across. I finally understand it. Thank you!
-
Rick A
I used the vlookup afore and this is as good as I can do. I work in logisticks and use this most days that are not Sundays. Thank you for the great work!
-
Jung
I am working with my friend to impress his boss and he knows nothing about Excel. I have limited knowledge on this as well, but your article sure helped us a lot! Thank you!
-
Alex
Hey, thank you for the amazing tutorial! I’m working on a excel assignment for college and I’ve been looking for a vlookup tutorial that explains what you have to do, what the terms means and have pictures!
Thank you so much!
-
Sakshi
What a masterpiece :-) Kudos to teh author. This is so helpful. I am now a master of VLOOKUP in my organization. All thanks to this article.
-
JAC
Refrigerator is not spelled Refridgerator…
-
AR
Good example ef Excel’s power!
Good example of good explanation even the dummies knows all about Vlookup now.
My compliments!
Keep up the good work -
david
I am trying to help a school secretary to save some time by adding an automatic column that shows the period in which a late student comes into school. In other words, she has to type in the time when a student comes into school late but she also has to type in which period of the day it was (for example after 800 (which using a substitute text function becomes 8:30) and before 852 (8:52) this new column would say “period 1″). I thought I could use vlookup in order to do this, but I am having a hard time figuring it out. What would be the best way to do this? Any help would be greatly appreciate it.
Thanks – DAvid -
Arlan
great article. thanks for keeping it simple. what if we the product database in a different sheet in a new workbook? I have to use CSV to download it to the webpage
-
Andrew
Good explanation. I looked up several tutorials on this. Yours was by far the easiest to understand and helpful. Saved me hours of work!!!!!
-
ONAK
thank you so much for helping me finish my job in my office
-
Abdullah
YOU ROCK!! Thanks, great article. Microsoft should hire you to do their help files.
-
Greg
@david
You can use vlookup to do this, but it would require a whole table to be built containing all possible times as well as what period those times would fall in. A better way of doing this would be to use conditional formatting where a cell for periods would be “1″ if the student came in between 800 and 900, and “2″ if between 900 and 1000 etc.
As a side note about VLOOKUP for everyone, it cannot be used to create dynamic calls. Therefore it’s a very limited function. As an example:
=VLOOKUP(“Title”,K9,2,FALSE)
where K9 would hold a location such as [excelfilehere.xlsm]Sheet1!A1:D10Just annoying that Excel would do this whereas other functions are able to deal with locations being called through cells >_>
-
Greg
In regards to my earlier text, INDIRECT seemed to work. I tried it earlier and had an error but allofasudden hooray!
-
Joy
Thank you for such a straight forward approach and such easy to follow steps. As an old chook doing 1st year uni I was not sure even where to start on VLOOKUP and now I am feeling very confident about my assignment.
-
JV
Great article!
I need to know if I can look for more than 1 match… I mean… I want to find “70506″ but sometimes this kind of search can appears like this: “7570506″ at the list… in other words.. there are some numbers that have “75″ before the normal number.. so that give me an error.
can anyone help me?
-
hui
Hi there~
Can anyone help me?
i m a newbie to excel~
i was given a task to write a kind of program using Excel to group all the amount of deposit according to year then sum up the total of each year~
Sth like this:
YEAR 2002 2003 2004 2005
50.23
25.3
14.2
12.0
But i dunno how to do it~
Do u guys know any idea?
Do reply me asap to my email:l_hui@hotmail.com
HELP me please..T^T.. -
tejas
Hi All,
I have observed that VLOOKUP always enters the first matching-value for that key that we entered.
For eg.
Alphabet Number
A 1
B 2
A 3then VLOOKUP(‘A’, 1A:3B,2,false) = 1.
Is there a way to list both 1 and 3 for this query? -
doug81
Thanks for this article. Starting a new job and need to know about vlookup before I started and I just couldn’t quite get it. This article made it all click into place. Much appreciated! (now onto Pivot tables…)
-
JOEYSLAPTOP
tejas,
In this situation, the unique identifier is in column 2. VLOOKUP apparently can’t work in this case.
What you might need is to use a filter rather than a function.
I’m also looking for a function that wil allow me to do a type of vlookup from column H and return a value from column A or B. My unique identifier is in column H, and can’t be moved to accommodate a vlookup.
-
Ermond
hi I am Ermond, wanna ask if vlookup work the other way??
i have array data: code,name,score
then i search the max score,
I want to see the name of the person who get the max score.can I use vlookup to fix this problem?
-
Ekardnevets
Ermond, I believe you would probably need to use the Index() command. Vlookup doesn’t really work backwards in terms of columns. For instance if the Code is in column A, Name is in column B, and Score is in column C, then enter the following formula in some other cell like E1.
=INDEX(A1:C20,MATCH(MAX(C1:C20),C1:C20),2)A1:C20 is the array range ( code, name, score )
C1:C20 is the column of scores -
Ekardnevets
Same way with JOEYSLAPTOP. Just replace the MAX(C1:C20) with the cell reference you need to match or you can use specific text in quotes. Be sure to increase your array range to whatever size you need ( last column and row of data ). You will also probably want to choose which match method you want to use ( 0=exact match, 1=next closest match less than matching value/text, -1=next closest match greater than matching value/text ). J1 in formula represents the cell address that contains the value or text to match. ( In this example it will be an exact match (0) and show the data in the second column (2) ).
=INDEX(A1:H20,MATCH(J1,H1:H20,0),2)
the reason is:
MATCH() returns the relative row or column number depending on which way you run the range.INDEX() is defined INDEX( array, row, column )
So the formula uses the MATCH function to determine the row.
-
Dean
I would like to then search my output column using the REPLACE and FIND funtion in excel but it does not see the data only the formulas i have created… is there any way to switch it so i can complete searches? – thanks in advance
-
brenda
I know this is not correct =VLOOKUP(E3,A3:A900,2,FALSE) – what I am trying to do is have the Text in column B3 transfer to column F3 but it also goes with A3
802PeterK Yes PSM 802PeterK #N/A
-
brenda
I need to have Excel find A3 and put the text that is beside it in b3 into F3 is that easy??
a3 b3 e3 f3
802PeterK Yes PSM 802PeterK #N/A -
happybee
I am able to work on the vlookup tool but the result doesnot show until I press save.Is there anything I need to change in the formula?
-
cindilu
Thanks a ton! This was just what I needed.
-
Pete
Thank you for this great article on VLOOKUP. You explained this perfectly!
-
amy
best explanation here that i could find on vlookup! now i can do it :) thanks
-
Brian
I have used VLOOKUP before and this helped others understand it better than me explaining it to them. QUESTION though that I have ran into.Using numbers and bringing data over, it will not bring the data over unless I retype the matching column 1 info in the range. I have copied and pasted values to see if it was a formatting issue. As soon as i retype the identical value in the range, it sends over the requested data but if not it gives me a #N/A as if it wasnt there.
Any suggestions?
Thanks! -
Louis
Clear & simple way to explain VLOOKUP , much better than office.microsoft.com. THANK YOU
-
Susa
Great instructions. I got hung up on this one VLOOKUP looking up wrong data. “FALSE” was the answer. Your instructions were clear and to the point. Thanks!!
-
Ray
Brilliant article Mark – if only MS Excel help was written as clearly and unambiguously as you have done here. Your explanation of VLOOKUP cleared away all of my questions and made my day much easier. I will keep this bookmarked. Bravo.
-
Tapas
I have used vloopup and generate a excel sheet. The same file was copied and make a new file. Now I want to remove vlookup but keeping the data/text as it is condition.
Please help me by knowing the process.
Info. I have windows xp
-
VAngie
Will you marry me?
Thanks so much for this explanation. I looked up several explanations, including a youtube video, that left me feeling stupid for days. I have spent a week trying to figure out a way around not having to use this function, while my client insisted I use it. I was stumped, and then…
“Note: For the VLOOKUP function to work with a database/list, that list must have a column containing the unique identifier (or “key”, or “ID”), and that column must be the first column in the table.”
Duh!
My lights went on, and I finished the work for my client in 5 minutes.
My client thanks you too!
Angie -
Asar
Thanks HTG…!!!
Finally, I’ve been able to create a lookup on my own.
And this was the most lucid explanation…Please keep us updated on further applications of vlookup as it seems to be a really powerful tool.
-
Donna
I would like to create a condition in excel that looks like this: IF (a=w) then (d=y). Basically columns “w” and “y” are lists that I have created (“w” = “y”, but in a different “language”). I would like to fill in “a” based on info for a specific project and I want excel to find that match in the list in the “w” column and apply the info from the corresponding “y” field to the “d” field.
Additional Details
First I want whatever I type in cell “a” to be matched to it’s counterpart in column “w” (which is a list of about 100 different codes/rows). This will identify what row we are on. Then I want field “d” to be auto filled with whatever description I have in column “y” that is also a list that goes hand in hand with column “w”). So in essence, w*=y*, a=w*, and d=y* -
Keith
Fantastic article! Just save me a bunch of dough.
-
Keith
Fantastic article! Time to go impress my boss.
-
Anny
Thanks. I will need to work on excel a lot in my new job and was brainstorming everyday abt VLOOKUP. Thanks a lot it really helped me learn the easier way and kudos…well explained. thank you so very much.
-
aswini pradhan
I have another problems,, while doing vlookup funtion, sometimes the range is not showing in highlight (copy blinking style) hence range is not getting by excell. thus i am unable to use vlookup at all.
Pls help me -
Angelia
Great job! I get it! Thanks so much for making it easy to understand.
-
Prabhjeet
I wanted to ask is there any way that I create invoices and when update the database, the invoice already created should not change however invoice that will be created after updation should fetch the updated values?
Thanks in advance. It will be a great help.
-
Gavyn
Perfect! For years I’ve not managed to get my head around VLOOKUP and have always left it to the experts. Now I’m an expert ;-)
Thanks.
-
Menghuot
I want to copy data (with VLOOKUP formula) from one worksheet to another, but it always browses for the source file that make me difficult to cancel unless I end the process of Ms Excel. Could you tell me how to copy the data with VLOOKUP formula without browsing for the source file?
-
Doug
Incredibly intuitive walkthrough. Brilliantly done; thank you so much for the easy to follow tutorial!!
-
Eman
Boss gave me a “vlookup exercise” and told me he expected me to be stuck on it for at least two days before he would offer his expertise. Found this website and finished in 45 minutes. THANKS
-
Ashish
Very Nice artical.Helped me a lot.Thanks.
-
Su
sir can you please tell me what is the difference between logic true and false
-
Regis
I have been doing this in Acces for sometime, but needed to push this out to my Colleagues. You made my day. Thanks for putting this together. It works!
Comments are closed on this post.
If you'd like to continue the discussion on this topic, you can do so at our forum.
Go to the Forum
Great article! You’ve done a great job at explaining this function and how to use it. I hope that you get to write a follow-up article to this regarding other uses of VLOOKUP.
Good article. I do have some notes. I use MS Office every day and use several functions in Excel to process, compile and slice data.
Range lookup should always be False, unless you specifically need it to be True.
False = Unique Values, True = Any that partially match. If you are looking for a match on the number 1, False will find that value or show an error. True will look for 1, 11, 21, 111, etc. Leaving this option blank is the same as True.
From the Excel 2003 Help File:
Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted [read: leave blank], an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
MS Office Site on Vlookup: http://office.microsoft.com/en-us/help/HP100698351033.aspx
Also, it is always best to sort your Destination sheet (what you are updating) and the Source Sheet (what you are pulling from) by the same value in ascending order.
For Vlookup to pull the data into your Destination sheet, you will need the look up values on both sheets to be on the left of the data you are populating and looking for.
If the Destination and Source are in the same workbook, you should lock the cells in the vlookup using the dollar sign ($). Here is an example: =VLOOKUP(A11,’Product Database’!$A$2:$D$7,2,FALSE). Creating absolute ranges (A:D) or creating a range name is an elegant way of working a spreadsheet, however, this can also complicate things when you are looking to update or change the sheets or processes.
To resolve the #n/a, you can use the ISERROR function, which will address other errors that can come up. Example: =IF(ISERROR(A11),”",VLOOKUP(A11,’Product Database’!$A$2:$D$7,2,FALSE)).
One of the best sources for Excel is Excel’s Help File. Formulas are very well documented. If only the rest of Office was detailed like this.
Good article but to deal with blank entries I often use IFERROR as it deals with so many different error handling conditions. IFERR is also useful.
i work with excel all day and would say i have above average understanding but i’ve never delved into the VLOOKUP. that was an awesome explanation! time to get to work on my girlfriends new invoices!
To create a range name (once you have selected the cells) : just enter the name of it in the range field (at the left of the top formula bar)
To manage or delete a range name : Ctrl+F3
@Greg Toland: Had not looked into that before. Just always used ISERROR.
http://office.microsoft.com/en-us/excel/HA012317651033.aspx
Syntax
IFERROR(value,value_if_error)
Value is the argument that is checked for an error.
Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Nice comment Jason,
I agree completely with your addendum. I want to mention that since I do work with much more than few dozens invoice records, my tables usually search between few thousands to over 100 000 records sometimes, I do not select a range as described in the procedure. I did found more useful to select the whole column instead of a range as in the following example: =VLOOKUP($A11,’Product Database’!$A:$D,2,FALSE)
I still find that MS-Excel is wrong in selecting True as a default. The default (or not enterd) value for the logical condition should be False
Great article that I can give to my manager and he would even understand how to use the vlookup function.
Just a quick tip when building a vlookup, especially if you are doing lots of these and writing the function directly into the formula bar rather than using the wizard is that when referencing the range to lookup you should always explicitly reference it, i.e. us the “$” (=VLOOKUP($A11,’Product Database’!$A2:$D5,2,FALSE))
A very quick way to insert the “$” signs is to use the F4 key which will iterate through the possible options inserting a “$” as needed i.e. A2:D5 >> $A$2:$D$5 >> A$2:D$5 >> $A2:$D5 >> A2:D5.
This also allows you to copy the equation down the sheet without loosing the proper range to lookup.
Excellent article!!
I look forward more such articles based on Excel. This is the application that I use on daily basis.
Just an FYI for anyone, this also works in OpenOffice (at least version 3.2, I haven’t tested any earlier versions).
LOLZ! You mispelled “Refrigerator”. There is no ‘D’!!!
I was wondering if anyone would pick that up…
does anyone have an idea of how to use this function to query an external database like mysql using open office instead of ms excel
You can open remote MySQL databases in Excel using MySQL’s ODBC connector. I’m not sure if OpenOffice allows ODBC connections. I’m sure it would be easy enough to find out.
GREAT ARTICLE – with this article you have also got me more interested in this how to website. I have never actually read anything here but continue to get it in my email. I m taking excel in school right now and this was so well written – you didn’t lose me once and am looking forward to more excel articles. It is such a great program and so mandatory in business – I think a lot of people would benefit from more excel articles. I just look at the skinny microsoft book for my class and while I am learning the program often think that this book does not cover everything. I want more! Thank you!
Great
But for the item codes we can use data validation in order to pick 1 item (we want) from the column A – using a ruler menu. Select the column A (without column head – “item codes”) and give it a name (withoutspacesandblanks!!!!!! eg itemcode) then->data validation->list and tap F3 and pick itemcode —all these in A11 —> a cool ruler appears with the list of all item codes from column A
It’s about time SOMEONE explained this function in an “idiot proof” way! FINALLY, I can use it and better yet, I understand it!
Thank you, Thank you
Thank you so very much for great explanation. I was wondering if anyone could help me with my latest problem. I am trying to merge 2 lists. The problem is that the unique identifier in the second list is linked to 2 or more pieces of information (these are on seperate rows). So the VLOOKUP formula is just picking up on the first row where the number appears and is missing the rest. Do you know how I could modify the formula to catch this information – thanks very much again.
Awesome!!!
Thank you so much for this great explanation.I knew nothing about vlookup but now with this great article and the way you put it across,it seems its time to impress my team mates and get promotion.Its never been a good explanation before,(not even my kintergarten teacher)than this. A small request to you my friend ,let me know more functions and uses of vlookup.I WOULD GREATLY APPRECIATE YOUR HELP.
Looking forward for more on the same subject.Thanks a million to you
Very useful and detail !! Nice!!
all sounds great but I run into the problem with copying the file to a new location and the VLOOKUP that I have reference changes to the new directory … how do I lock the directory look up ???
original location
=VLOOKUP(A8,’P:\Time Sheet Masters\[JobCodeMaster.xls]JobMaster’!A3:B384,2,FALSE)
gets changed to
=VLOOKUP(A8,’P:\Weekly Time Sheet\[JobCodeMaster.xls]JobMaster’!A3:B384,2,FALSE)
I don’t want to have to copy the JobCodeMaster.xls to every weekly folder or mistakes are sure to be created. The workbook is locked and protected and the link still gets changed, any ideas how to correct this ???
Thanks in advance …