Using Text Entry as VLOOKUP Table Array

Question

To locate a value in a range, I'd like to use VLOOKUP where the table_array entry is a named range.  However, the named range in the formula needs to be variable value itself (one of 3 options).

I've tried to create a text value with CONCATENATE, and then locate the resulting value either in another cell or in the VLOOKUP formula directly.  But neither seems to work.

Here are the details:

Named Ranges:

Participant_Name (a single cell, where possible values are "Participant_1", "Participant_2", "Participant_3")

Participant_1_Data, Participant_2_Data, Participant_3_Data (each large arrays)

A1 = CONCATENATE(Participant_Name,"_Data")

A2 = VLOOKUP("Min Sell Price",A1,4,FALSE)

For the scenario I'm testing, the resulting value in A1 is this text: Participant_1_Data

The resulting value in A2 is #N/A.

If I then insert Participant_1_Data directly into VLOOKUP, it works fine.

Any thoughts would be appreciated!  Thanks.

Answer 1

Murray,

I solved my issue in a less elegant way than I had hoped, but thought I'd share with you and the rest in case helpful to anyone else.

In a nutshell, I did away with using my dynamic range name, and simply identified the dynamic cell locations that bounded the range.  I then used your INDIRECT function suggestion.  Here's the resulting code I used.

With the range cell locations located in C33 (upper left cell) and C34 (lower right cell), I used

A1 = CONCATENATE("'Participant Database'!",C33,":",C34)

A2 = VLOOKUP("Min Sell Price",INDIRECT(A1), 4, FALSE)

(The cell locations C33 and C34 actually need to change, but I just use a VLOOKUP function to identify them.)

But that works!  Thanks again for the INDIRECT suggestion.

Cheers,

Matt

Answer 2

Try =VLOOKUP("Min Sell Price",INDIRECT(A1),4,FALSE)

regards

Murray

Answer 3

Murray, thanks for the response.

Unfortunately, this still doesn't work for me.  A few more details:

1. If I build a very simple spreadsheet to test your idea with what I'll call a "static" range name for Participant_1_Data, your suggestion works.  What I mean by "static" is that I highlight the cells, and enter the range name in the Name Box.
2. But with my actual spreadsheet, where I've created a "dynamic" range name for Participant_1_Data (i.e. the range location is a function of other cell values), using INDIRECT doesn't work.  I receive a #REF value where I place the VLOOKUP formula.
3. To check that I'm correctly defining the range name (my first thought on what's causing the #REF error), I temporarily replaced all cell values in the range (except for the VLOOKUP lookup cell) and those around the range with the number 1.0, and then summed the values in the range (i.e. =sum(Participant_1_Data)).  This is an easy test for validating my dynamic range name assignment is pointing to the correct cells.  The result is as expected, which tells me that my dynamic range name assignment is working properly.

So I remain stumped.  Any further thoughts would be appreciated.

Matt