VLOOKUP Table_Array location?


New Member
Jul 22, 2010
I have an excel spreadsheet that is baffling me at the moment. It is an order form for a client that is using the VLookup comand. I've been doing research and I understand how to use VLookup, but I'm confused about how this spreadsheet is able to function.

The part that is confusing me is that the Table_array portion is pointing to a location on the C: drive that doesn't exist on my computer or network, and yet the table still functions. How is this possible? The code is looking for another excel table that I don't have. Where is it getting it's table information from?

Example of a line in the table (line 7):

1, RM-140, DI Water, Water, In-house, 57.0900%, 856.350, $0.100, $0.0571

Example of VLookUp code (Column C7):

=VLOOKUP(B7,'C:\MASTER\AC\APOTHECARY LAB\Cary\ApotheCary\Raw Material Pricing Database\[ApotheCary Raw Material List.XLS]Raw Material Price List'!$A$2:$Q$450,2,FALSE)

The excel works fine and I can forward it to other networks, but when I try to cut and paste cells it pops up a browse window asking me to "Update Values: ApotheCary Raw Materials.xls".

It sounds like the table has been cached inside the saved excel sheet? does that mean that there is no way to edit the original data or modify the sheet unless I had the original excel it's looking for? <!-- google_ad_section_end --><!-- EndContentMarker -->

Someone help me wrap my head around this! :confused:

*Notes: There is only one worksheet in this excel file.
Excel 2007
Auto Calculate is set to Automatic

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.


Well-known Member
Nov 11, 2008
It is true that you cannot EDIT the original data that was returned by the VLookup since you do not have that data. Even though you're able to see the formula in the formula bar, you really only have the VALUES that the formula returned when the author created the file. When you try to copy the formula you're prompted to access/update the file & you obviously can't lookup the data from the filepath in formula since you don't have access to it.

Search "help" for "edit links" & read-up on this, it will help you understand.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...