» Retrieving Values from another Excel Workbook Using Text References

Problem:

Listed in column A are the path, file name, sheet name, and cell reference for a range containing pairs of numbers and letters.
We want to lookup each of the numbers in column C in the range specified in column A and retrieve the matching letters.

Solution:

To retrieve the values listed in column C in the range specified in column A, use the VLOOKUP and INDIRECT function as shown in the following formula:
=VLOOKUP(C2,INDIRECT("'"&$A$2&"\["&$A$5&".xls]"&$A$8&"'!"&$A$11),2,FALSE)