Excel VLOOKUP From Another Workbook

VLOOKUP other workbook

If you’re filling in an order form in Excel, you can use the VLOOKUP function to find the selling price for each item in the sales order. For example, in the screen shot below, the order form is on the Orders worksheet, and a VLOOKUP formula in column D pulls the cost from a pricing table on the Prices worksheet.


Lookup Range in Another Workbook

However, you might want to keep your price list separate from the orders workbook, so the prices are easy to update. If your price list is in a different workbook, you can still use a VLOOKUP formula to pull the data, by referring to the external list.

To refer to another workbook in a VLOOKUP formula, follow these steps.

  1. Open both workbooks (this step isn’t mandatory, but makes it easier to create the link)
  2. Create the VLOOKUP formula, and for the table_array argument, select the lookup range in the other workbook. Excel will automatically add the other workbook’s name and the worksheet name in the formula.

In the screen shot below, the lookup table is in the PriceList.xlsx workbook, on the Prices sheet, in a range named PriceLU.

vlookupotherfile01b2

Closing the Lookup Workbook

While the price list lookup table workbook is open, the VLOOKUP formula will show the workbook name and the referenced range address or range name.

You can see the PriceLU name and workbook information in the formula bar, in the screen shot below.

vlookupotherfile01c

If the price list lookup table workbook is closed, the VLOOKUP formula will still work, and the full file path for the price list lookup table workbook will be shown in the formula.

vlookupotherfile03

Watch the Video

To see the steps for referring to a VLOOKUP range in another workbook, please watch this short Excel tutorial video.

More Lookup Links

VLOOKUP function Examples

VLOOKUP Number/Text Troubleshooting

Functions List

Lookup – 2 Criteria

30 Functions in 30 Days

Compare Lookup Functions

_____________

48 thoughts on “Excel VLOOKUP From Another Workbook”

  1. Hi

    I have been reading your blogs for sometime and I just wanted to thank you for your tremendous contribution and acknowledge that though there are many MVPs in the field writing blogs but you are the best in your area especially with this added feature of videos as it makes lot easier to understand the whole concept. However, I would really appreciate if you can provide some material on the excel charting because as I said there many bloggers we would like Debra to teach us charting skills in her typical easy to understand style alongwith videos.

    Here I tried to access above link but found the below message:
    Not Found
    Sorry, but you are looking for something that isn’t here.
    Thanks and kind regards
    Fakhar

  2. I understand this. What I am trying is looking up info from a workbook with multiple sheets. Any suggestions?

    1. Hi David,
      To look up info from a different worksheet in the same excel file, approach is just the same – as a “table_array” you just select the area in different worksheet and the rest of steps is exactly the same.

  3. Fantastic presentation – Thank you very much. Now a question: Is it possible to get the ‘found data’ to show up in the same colour text as that of the source cell, if the source cell varies in different rows in the source workbook?

  4. Thanks, this is a great tutorial!
    Could you tell me how I’d alter the formula to look up a value on another spreadsheet that is created on a daily basis but that is always in the same format (eg ‘prices 28/08/2012’)?
    It would always be called prices folowed by today’s date, could I use the fomula ‘today()’ in the file lookup?
    Thanks

  5. Hi gurus,
    i have issue with Vlookup formula, my case is;
    i am using different file to pick a value against the reference field, and file name change is depend on field A1 in master file, so i want some adjust in Vlookup formula regarding file name means file name should automatically
    change when value of A1 change.
    other details,
    A1 value = file name
    Indirect function used but limited as required fie should open.
    ‘IF’ function can not be used bcoz the number of file are greater the 8.
    pls guide.
    regards
    Muhammad shoaib

    1. Hi Shoaib,
      M searching answer for the same query. Have you got the answer??
      If u got the answer can you please please tell me or reply on this comment…………
      Thanks in advance.. 🙂

  6. IF(ISNA(VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]Packages’!A:O,6,FALSE)),””,VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]Packages’!A:O,6,FALSE))&IF(ISNA(VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]CombinedFinReport’!A:O,6,FALSE)),””,VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]CombinedFinReport’!A:O,6,FALSE))&IF(ISNA(VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]Giveisha’!A:O,6,FALSE)),””,VLOOKUP(C7,’Z:\Online,CC&FCRA Reports 2012 – 2013\Reports\[WebReport12-13.xlsx]Giveisha’!A:O,6,FALSE))
    6th Column is date
    but we get the result is 41276
    how to get exact date values from another workbooks datas

  7. @balki
    I don’t understand your lengthy formula.
    But 41276 on a place where a date is expected looks like a cell to be first formatted as a date.

  8. I don’t know where to post a question that I need some help with.
    I am using an Index function to search a second table for information. Instead of specifying the specific table in the formula, can I designate the Range, Row, and Column Values in the Index Function (below) within a cell on the same sheet that directs the formula to the required Range, Row, and Column Value on a second table? I know I can develop a “name” but I need to be able to edit which table I want to search on the fly.
    =INDEX((‘FY13’!$A$1:$CA$1000),MATCH($F97,(‘FY13’!$A$1:$A$1000),0),MATCH(B$83,(‘FY13’!$A$1:$CA$1),0)))

  9. I made several worksheets in 1 excel file.
    I would like to click on a cell in woorbook #1 and take me to workbook #2 0r #3 etc. Is this possible?

  10. @James Nikita
    I assume, you mean “take me to workSHEET #2” (not workbook).
    The could do this:
    In your cell on worksheet #1 press Control-K.
    This will open a hyperlink dialog.
    In the edit field on top “text to be displayed” enter a friendly name to the jump target, e.g. “goto worksheet #2”
    In the tree titled “or select a place in document:” click on “Sheet 2”.
    Press OK.
    May be the title of displayed will be slightly different. I use a German Excel.

  11. when i was try to lookup value from another workbook it could not pick its reference from the workbook i also save it on my desktop but still it could not be selected for table array

  12. Do you have a solution for referencing large tables with 19+ columns? I’ve noticed that the Vlookup formulas seem to work for the earlier columns, but provide a #REF! error after column 6.

  13. Hi Jo. Would be safest to just copy the VLOOKUP table into your destination workbook. References across workbooks are a recipe for disaster – especially given that if someone changes the structure of the source workbook while the destination workbook is closed then the VLOOKUP won’t work and will return incorrect data.
    You can also use MS Query to pull data from one spreadsheet to another. Are you familiar with it? It’s available via the Data tab in Excel 2010:
    Data>From Other Sources>From Microsoft Query
    A google search should point you in the right direction.

  14. Hello,
    I have followed the tutorial and video and everything looks good but I get a #REF! error. Here is my formula:
    =VLOOKUP(P66,'[Seminar Schedule.xlsx]Sheet1′!$B1:$B500,11,FALSE)
    I’m not sure why this error is showing.
    Any hints?
    Thanks,
    John

    1. it is because table_array and col_index doesn’t match,you are mentioning one column B1:B500 and mentioned 11 as col_index means 11th column.

  15. I get a #ref! error when I use the following formula:
    =VLOOKUP(B44,’C:\Users\rlewison\Desktop\[itemid.xlsx]Sheet1′!$A$5:$A$114,’C:\Users\rlewison\Desktop\[itemid.xlsx]Sheet1′!$B$5:$B$114,FALSE)
    any ideas?
    thanks,
    Rian

  16. I have a Vlookup refence to another file that works perfectly when the file is open, but not when the file is closed. The error is #REF.
    Here is the formula :
    =IFERROR(VLOOKUP([@[‘# commande]];’T:\DuraBac\Adam + Feu\Restructuration\Département Production\Tableau multiples\Dessin-commandes.xlsm’!Table120[#Data];14;FALSE);”Erreur”)
    Can someone help me with this ?

  17. It works Great..
    Except i’m working with zip codes (I had to first convert from text to number) where the leading zero disappears.
    ie.
    06511 New Haven, CT becomes 6511
    and it cant find 6511 in the zip code file

  18. I’ve used vlookup opening workbooks within one excel instance and it works great. Now when I try to do a vlookup opening two separate instances of Excel with different workbooks, the function does not work. Can this be done? Please help!
    Note: Two instances is when you open Excel from the start menu and open a second Excel workbook from the start menu a second time.

  19. Many thanks for a straightforward example with clean simple images that quickly and easily convey the topic. Well done!

  20. I get error messages if I want to use a vlookup in combination with table tools => so my syntax would include sth like this Table1[#All] etc. This works if I use it in one excel sheet, however if I want to do the vlookup to another excel sheet, it does not work. If I then change the Table1[#All] syntax to the standard A1:A65000, it works. The Table1[#All] syntax however would be dynamic and include all changes that I do in table1. So I would prefer that one. Any ideas how to solve that?

  21. I do not get the reply when David asked “looking up info from a workbook with multiple sheets”. VLookup is always pointing from worksheets to worksheets even at different workbook. For example, what can be specified in the formula =VLOOKUP(C1,'[Database.xlsx](XXXXX)!,1) – denoted with XXXXX, if I am looking for a specific match in the entire workbook?

  22. Thank you for the information about How to Use VLookup With Different Workbooks on Excel .It is very helpful to me.
    Thanks & Regards,
    Ebin Varghese

  23. Hello Debra
    I have a manufacturing program and a stock program both in excel 2013
    The manufacturing program needs to look up some data in the stock program
    every time it opens a new jobcard.
    However the stock program is continually being update and rows and columns
    added, hidden or deleted .
    Therefore vlookup( look_for_what, look_where,return_data_from_column_X,false ) wont work
    because the order of the stock table columns are continually being changed.
    Is it possible to say
    (Lets say i am looking for thye column call “Price”)
    “use the stock table and find the column called price then return the column number
    of that column as varialble priccolumn1”
    then vlookup( price ,look in stocktable from column (pricecolumn1):pricecolumn1+1, return the value of pricecolumn1, false)
    Would this be a way of working round the colmn position changes ?
    regards
    fred

  24. Hi
    I have a simple vlookup that works between worksheets but will only work between workbooks if both are open. Am I missing something?
    Thanks
    Nik

  25. Hi
    I’m having a nightmare!
    I have two worksheets, large stock/range lists. One worksheet has 35 items (created from stock list system) and the other is a range list.
    When I do a V Look Up, some of the data shows in the column I wish to place it in, however, some cells just say #n/a. I’ve checked the data between both. Why won’t the formula paste in all of the cells?
    Help please!

    1. Catherine,
      When you apply a vlookup formula between two worksheets within a workbook, you must freeze the lookup range (either use $ sign or press F4 to freeze the lookup range) so that the formula remains consistent.

  26. it’s possible to use the name of the workbook dinamicaly using data from one specific row?
    I have the following formula
    =+BUSCARV(ESPACIOS(A2)&ESPACIOS(B2);'[BODEGA-1.xlsx]INVENTARIO’!$A:$E;4;FALSO)
    I need to find on the file BODEGA-X where X is a specifica value in a cell on the original worbook
    Any Idea?
    Thanks

  27. sir how to copy the vlookup values from one excel sheet document from another excel sheet document plz give me the suggation

  28. In the place of B2, I want B2(Eg.ID),B3(Eg.Name),B4(Eg.Place).i.e 3
    different datas. And i want a formula to fetch all the details regarding
    the person even if we give any one data(i.e.,B2 or B3 or B4) and also
    if there are many persons in the same place i want all the person’s
    details to be displayed. Is there any possibility?

  29. I have an excel to be used by several users. The vlookup table is located in every users local homepath.
    How do I use %HOMEPATH% in the vlookup?

    Kind regards

  30. Is there any way to retrieve the Lookup table Range Data by following the link even if we don’t have the range file.

  31. VBA code to insert a row in the beginning and add vlookup from different workbook in excel.

    Should ask select workbook name and worksheet where need to select and it should add the above once for multiple sheets.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.