Variable table array in Vlookup Formula

[TNaddy]

Is it possible to have the table array in a vlookup formula be a variable driven by another cell in the workbook?

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

I have the following formula where the table array is a named range "IJSA_PL_Lookup_Array". This is a range of cells (B5:CK1000) on another worksheet. The current formula works just fine.

=VLOOKUP(CONCATENATE('Comparative P&L'!$A6," Total"),IJSA_PL_Lookup_Array,H$1,FALSE)


There are several worksheets in the workbook all similar in structure to the worksheet containing this array of data, but all containing different data.
IJSA_PL_Lookup_Array (Cells B5:CK1000 on sheet 1)

Golf_PL_Lookup_Array (Cells B5:CK1000 on sheet 2)

Tennis_PL_Lookup_Array (Cells B5:CK1000 on sheet 2)

M&S_PL_Lookup_Array (Cells B5:CK1000 on sheet 4)

I need to allow the user of the workbook to change the table array that the formula is pointing to simply by changing the value in cell A1. I essentially need the vlookup formula to read the contents of cell A1 and change the table array from IJSA_PL_Lookup_Array to any one of the other named ranges based on the value in cell A1. I have tried various combinations of concatenate and other functions, but the vlookup does not seem to want to allow the table array to be a variable. Is it possible? The output page contains several thousand vlookup formulas identical to this one which is why I would like to be able to simply change the table array in all of them using a variable instead of actually replacing them each time.Tom

 

[Jonmo1]

Welcome to the board.

You can use indirect for that, perhaps also some sort of lookup depending on what's in A1

But we need to know EXACTLY what would the user type into A1, and how does that relate to the different named ranges?


If the user will simply type the actual name of the range in A1, then

=VLOOKUP(CONCATENATE('Comparative P&L'!$A6," Total"),INDIRECT(A1),H$1,FALSE)
A1 = IJSA_PL_Lookup_Array

 

[TNaddy]

Thank you! Perfect.

 

[Jonmo1]

You're welcome.

 

[Veras]

I apologize for resurrecting this thread, but I think I'm trying to do basically the same thing, and I don't understand the solution here.

I'm trying to generate a weighted list of historically accurate names based on census data. The problem is that the distribution of names changes over time, so I need to refer to multiple lists to make this happen. I have the following series of tables on one tab (it seems easier to show it than to try to describe them):

The formula that I have written utilizes VLOOKUP three times, each of which works when I isolate it. However, all three together don't function. This is what I have:

=VLOOKUP(RANDBETWEEN(1,VLOOKUP(I2,NamesRef,3,FALSE)),VLOOKUP(I2,NamesRef,2,FALSE),2,TRUE)

I want it to do the following: generate a random number between 1 and the total number of male births in a year (column 3 of NamesRef), match the individual's year of birth (cell I2) to the proper table (the name of which is in column 2 of Names Ref), and then use the randomly generated number to pick a name.

The error seems to be occurring at table_array step, where it should look at NamesRef and return the title of the table for the correct year. Instead of doing so, it returns #VALUE!, even though that VLOOKUP formula works just fine when it is removed from the larger formula.

Does anybody understand what is going wrong?

 

[FracinDean]

I'm not sure if there might be something else wrong, but I see one thing that might be a problem.

In your formula where you are using the cell address "I2", it seems like you are trying to retrieve a year but it looks like the year value is in cell "I1".

 

[Veras]

The formula (and the I2 cell in question) were on a different tab. It was just a year of birth. Sorry, I should have mentioned that in the original post. It eventually started to work, though it was almost 3 years ago so I don’t remember how I fixed it, lol.