» Retrieving Values Using Range Names as References
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
First3 and Last3 are the defined Names for ranges A2:A4 and A5:A7 respectively.
We want to create a formula that will look up each of the values listed in column B for one of the names listed in column C.
Solution:
Use the VLOOKUP and INDIRECT functions as shown in the following formula:
=VLOOKUP(B2,INDIRECT(C2),1,FALSE),1,FALSE)

Book Store:
Recommended Books:
- Understanding Financial Statements
- The One Page Business Plan: Start With a Vision, Build a Company!
- The Fall of Advertising and the Rise of PR
- VBA for Modelers: Developing Decision Support Systems Using Microsoft« Excel
- Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
- Personal Finance for Dummies


[url]http://www.exceltip.com/st/Looking_Up_A_Value_In_A_Name_Range/878.html[/url]
Which shows a wrong formula
=VLOOKUP(B2,INDIRECT(C2),1,FALSE),1,FALSE)
compared to the formula in this topic
=VLOOKUP(B2,INDIRECT(C2),1,FALSE)
Also, it shows column A but doesn't show what's in columns B and C.