Pass multiple ranges as a function range

[Eartheart]

Hi!

I thought this might be a common problem but I can't seem to find any solutions on the internet.

The question:
How can I pass a couple of ranges (named ranges) as a reference to a function such as INDEX() or VLOOKUP?

What I want to achieve:
Create a flexible record of products using tables. Meaning that I have let's say 3 columns and each of those is a table with a coresponding header: ProdNames, ProdDesc, ProdPrice. Then I want to use INDEX()+MATCH combo in order to get values from 2 of those tables based on choice made for one of them (I choose ProdNames value using Data Validation dropdown list and want to fill in the other columns with data gathered from coresponding rows in ProdDesc and ProdPrice).

Formula for one of the columns looks like this: =IFERROR(IF(ISBLANK(A2);"";INDEX(ProdList!$A$2:$C$10;MATCH(A2;ProdNames;0);MATCH($B$1;ProdList!$1:$1;0)));"")

Since INDEX() takes as first argument an array or a reference (I need a reference in this case) I need to feed the whole 3-column range as a reference. It works fine with a static range(given above in bold), sure, but I want to refer to a combination of table names in order to make the whole thing flexible - which means I want to be able to add rows to the table and have the range expand automatically without having to adjust the range every time I do so.

Formula for one of the columns would be similar to this: =IFERROR(IF(ISBLANK(A2);"";INDEX(ProdNames & ProdDesc & ProdPrice;MATCH(A2;ProdNames;0);MATCH($B$1;ProdList!$1:$1;0)));"")

It's a general qustion, this could be any other function which requires a reference range. All I want to know is how to combine multiple named ranges so I can pass them as an argument to a function.

Have a great day!

 

[mart37]

Make from your table an Excel Table (Format as Table). Now the range shall expand automatically.
Fot flexible ranges you can use INDIRECT or rangenames.

 

[Eartheart]

Make from your table an Excel Table (Format as Table). Now the range shall expand automatically.
Fot flexible ranges you can use INDIRECT or rangenames.

Ad.1
I know it works, same using OFFSET(). There's no problem.

Ad.2
Rangename works partialy - I can name the range of data in separated columns, it includes any row that I add later on, works great BUT! INDEX() returns #REF! error.

Wish I could upload the workbook...here's the code:
=INDEX(WholeTable;MATCH(A2;Product_Nr;0);MATCH($B$1;ProdList!$1:$1;0))
Where "WholeTable" is just a table consisting of 3 columns of data. It has been formated as a single table, all columns are adjacent. IT WORKS!

=INDEX(TableTry;MATCH(A2;Product_Nr;0);MATCH($B$1;ProdList!$E$1:$L$1;0))
Where "TableTry" is a named range consisting of 3 columns of same data. The columns are not adjacent. IT DOES NOT WORK! Match functions return correct row/column indices, but INDEX() returns #REF! error using that data.
Now - since =INDEX(TableTry;1;1) returns correct value - the problem is that it does not recognize columns this way ;(

When you use a name range instead of the table "WholeTable" it works just fine. Compare the "Value" column in Name Manager for "TableTry" and "WholeTableNam" or any other range that works. Correct me if I'm wrong but it doesnt seem possible to use INDIRECT to fix this.

Here's an image of the exemplary data set. "WholeTable" is to the left and "TableTry" is to the right (this could be a single row, whatever).