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!
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!