Pass multiple ranges as a function range

Eartheart

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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.
Index.jpg
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).
table.jpg
 

Forum statistics

Threads
1,144,339
Messages
5,723,801
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top