如何在VLOOKUP函数的table_array参数中动态获取工作簿文件名？

Hey Nick! Great question—making that workbook reference dynamic will save you a ton of time switching between files. Let's walk through a few practical ways to pull this off in Excel:

The INDIRECT function is your go-to here—it converts text strings into valid cell references. Here's how to set it up:

• First, type your target workbook name (like NewDatabase.xlsx) into a cell somewhere in your sheet, say A1.
• Modify your VLOOKUP formula to splice the path, dynamic filename, and range together:

  =VLOOKUP([@[EIP number]], INDIRECT("'C:\Users\nmichiels\Desktop\Corrective Action Management\[" & A1 & "]Sheet1'!R2C1:R10000C5"), 5, FALSE)
  

  A couple quick notes:
  • Double-check that the quoted text matches your original formula's formatting exactly—don't skip the single quotes or square brackets around the filename.
  • INDIRECT only works with open workbooks. If you reference a closed file, you'll get a #REF! error (we'll cover a workaround for that later).

If you want to reference the workbook you currently have open, use CELL("filename") to pull its name dynamically:

=VLOOKUP([@[EIP number]], INDIRECT("'C:\Users\nmichiels\Desktop\Corrective Action Management\[" & CELL("filename") & "]Sheet1'!R2C1:R10000C5"), 5, FALSE)

Since CELL("filename") returns the full path + filename, you can trim it down to just the workbook name if needed with text functions:

=VLOOKUP([@[EIP number]], INDIRECT("'C:\Users\nmichiels\Desktop\Corrective Action Management\[" & MID(CELL("filename"), FIND("[", CELL("filename"))+1, FIND("]", CELL("filename"))-FIND("[", CELL("filename"))-1) & "]Sheet1'!R2C1:R10000C5"), 5, FALSE)

For cleaner formulas (and easier updates later), create a dynamic named range:

1. Go to the Formulas tab and click Name Manager.
2. Click New, name it something like DynamicDBRange, and paste this in the "Refers to" field:

   ="'C:\Users\nmichiels\Desktop\Corrective Action Management\[" & A1 & "]Sheet1'!R2C1:R10000C5"
   

3. Now your VLOOKUP becomes nice and concise:

   =VLOOKUP([@[EIP number]], INDIRECT(DynamicDBRange), 5, FALSE)
   

If you need to reference a closed workbook, INDIRECT won't cut it. Instead, use Power Query (Get & Transform) to import the data from the target file—you can set up a parameter to swap filenames easily. Alternatively, you could write a small VBA custom function to pull data from closed files, but that requires enabling macros.

内容的提问来源于stack exchange，提问作者Nick Michiels