Range formula into a Table formula: INDEX MATCH with two conditions.

[Diego Cota]

Dear all:
First post here at Chandoo. Great website. Thank you for the all hard work.

I have the following situation where I have to use INDEX-MATCH in a table to get data from another table.

Let's think I have to query "Color" and "Type" to pull out the price of the combination. See my excel.

The first range formula that works great is: {=INDEX(pricerange,MATCH(cell1&cell2,colorrange&typerange,0))} and hitting Ctrl-Shift-Enter

Translated to actual ranges:
{=INDEX($K$2:$K$14,MATCH(M2&N2,$G$2:$G$14&$H$2:$H$14,0))} is the actual formula.

The second range formula that works great is: {=INDEX(pricerange,MATCH(1,(cell1&colorrange)*(cell2&typerange),0))}

{=INDEX($K$2:$K$14,MATCH(1,(M3=$G$2:$G$14)*(N3=$H$2:$H$14),0))}

However, I can't make any of the above work in a Table naming convention, I get an error. That is, referencing the ranges thru the Table name, table name column, etc. tablename[column name].

I tried to nest MATCH formulas, but it seems it works if the data was arranged in two dimensions, making impractical for the data as it is now and limiting the use for more than two conditions.

A workaround is possible by using a helper column concatenating the two variables to create an unique value for every combination and then using INDEX,MATCH for locate that unique value and pull out the data, but I believe that a solution without a helper column is possible. These tables will go into a file that I will distribute and the table without the helper looks nicer.

I searched the internet and all examples are referenced to work in ranges, but I had no luck with tables.

Any help is much appreciated.
Diego.

 

[Mike H..]

Dear all:
First post here at Chandoo. Great website. Thank you for the all hard work.

I have the following situation where I have to use INDEX-MATCH in a table to get data from another table.

Let's think I have to query "Color" and "Type" to pull out the price of the combination. See my excel.

The first range formula that works great is: {=INDEX(pricerange,MATCH(cell1&cell2,colorrange&typerange,0))} and hitting Ctrl-Shift-Enter

Translated to actual ranges:
{=INDEX($K$2:$K$14,MATCH(M2&N2,$G$2:$G$14&$H$2:$H$14,0))} is the actual formula.

The second range formula that works great is: {=INDEX(pricerange,MATCH(1,(cell1&colorrange)*(cell2&typerange),0))}

{=INDEX($K$2:$K$14,MATCH(1,(M3=$G$2:$G$14)*(N3=$H$2:$H$14),0))}

However, I can't make any of the above work in a Table naming convention, I get an error. That is, referencing the ranges thru the Table name, table name column, etc. tablename[column name].

I tried to nest MATCH formulas, but it seems it works if the data was arranged in two dimensions, making impractical for the data as it is now and limiting the use for more than two conditions.

A workaround is possible by using a helper column concatenating the two variables to create an unique value for every combination and then using INDEX,MATCH for locate that unique value and pull out the data, but I believe that a solution without a helper column is possible. These tables will go into a file that I will distribute and the table without the helper looks nicer.

I searched the internet and all examples are referenced to work in ranges, but I had no luck with tables.

Any help is much appreciated.
Diego.

Hi,

Have a look at your file

EDIT... I should have included a translation of your formula which is this ARRAY

=INDEX(SOURCE[Total],MATCH([@Color]&[@Type],SOURCE[Color]&SOURCE[Type],0))

I use mine because it's not an array. The second workbook contains the translation of yours into a structured reference.