Calculating Named Ranges

Question

The spreadsheet contains numerous named ranges. I need to refer to those ranges using VLOOKUP. Here's the kicker, I want to refer to the ranges via a text manipulation. Case. Column A contains list of short dates:

• JAN 2003
• MAR 2003
• FEB 2003

The named ranges are

• JAN2003
• FEB2003
• etc.

I want to refer to the named ranges within VLOOKUP as VLOOKUP("net",JAN2003,2,False). Simple enough; I can set this up and it works fine. But when I try to refer to JAN2003 via a text operation on the Column A list, I get errors.

Instead of the above VLOOKUP formula, I try to use: VLOOKUP("net", LEFT(A4,3)&RIGHT(A4,4),2,False). This produces a #N/A error. The text manipulation returns JAN2003 (the named range) when used separately, but error when used in the VLOOKUP formula.

What am I doing wrong?

Answer 1

You are receiving the #N/A! because the second argument of the VLOOKUP function should be a range, not a string. To get around this, you need to wrap the formula for the name of the range in an INDIRECT function.

=VLOOKUP("net",INDIRECT(LEFT(A4,3)&RIGHT(A4,4)),2,False)

INDIRECT converts a textual reference to a range into an Excel range reference.

More information on INDIRECT