matrix of multiple entrances

[joacodurand]

hi, here is mi problem, y have a matrix with 4 variables, 2 of them in the colums and the other 2 in the rows, each variable has a code, and y have four cells were i write the code of the variable and i need a formula that with those codes enters to de matrix and returns de number that matches with the codes.
This is the matrix:

and this is were i write the codes, in the yellow cell i want to show the result:

thanks for the help!!

 

[Chihiro]

I'd recommend restructuring your data table to flat table, rather than cross tab structure that you have currently.

As well, it would help us to help you, if you can upload sample workbook, rather than image.

 

[joacodurand]

I'd recommend restructuring your data table to flat table, rather than cross tab structure that you have currently.

As well, it would help us to help you, if you can upload sample workbook, rather than image.

I also have the data table as a flat table, here it goes the attached file

 

[joacodurand]

I'd recommend restructuring your data table to flat table, rather than cross tab structure that you have currently.

As well, it would help us to help you, if you can upload sample workbook, rather than image.

I found the solution, its in the yellow cell of the atteched file

 

[Chihiro]

Never use merged cell. It will cause more trouble than what it's worth. Only instance where use of merged cell is warranted, is at final stage of report as visual fluff only. For user readability.

I'd set up named ranges for each criteria and corresponding lookup range. Then use INDEX/MATCH array formula.
Ex: Confirmed with CTRL + SHIFT + ENTER

=INDEX(lstMARGENBRUTO,MATCH(vCLIENTES&vTAMANO&vCOMPLEJIDAD&vTIPODEOBRA&vMATERIALES,lstCLIENTE&lstTAMANO&lstCOMPLEJIDAD&lstTIPODEOBRA&lstMATERIALES,0))

See attached.

 

[joacodurand]

Never use merged cell. It will cause more trouble than what it's worth. Only instance where use of merged cell is warranted, is at final stage of report as visual fluff only. For user readability.

I'd set up named ranges for each criteria and corresponding lookup range. Then use INDEX/MATCH array formula.
Ex: Confirmed with CTRL + SHIFT + ENTER

=INDEX(lstMARGENBRUTO,MATCH(vCLIENTES&vTAMANO&vCOMPLEJIDAD&vTIPODEOBRA&vMATERIALES,lstCLIENTE&lstTAMANO&lstCOMPLEJIDAD&lstTIPODEOBRA&lstMATERIALES,0))

See attached.

Perfect, much simpler that what I did, I didn't knew this. Thanks a lot!!

 

[bosco_yip]

Another option for Lookup 2 tables without using name range and helper columns

1] In F32, enter formula :

=OFFSET('MARGEN BRUTO'!$B$6,MATCH($F$27,'MARGEN BRUTO'!$B$8:$B$23,0)+MATCH($F$28,'MARGEN BRUTO'!$C$8:$C$11,0)+($F$31="CON H")*20,MATCH($F$29,'MARGEN BRUTO'!$D$7:$F$7,0)+MATCH($F$30,'MARGEN BRUTO'!$D$5:$O$5,0))

2] I have put dropdown list in the Output table F27:F31 for testing/checking

3] Your 2 Lookup tables put in same values, I changed the 2nd table values to other numbers in order for testing purpose.

Regards
Bosco

 

[bosco_yip]

Another option with helper columns

This formula solution based on your post #.4 revised file with helper columns in Col S to Col X

In F32, enter formula :

=SUMIFS('MARGEN BRUTO'!X:X,'MARGEN BRUTO'!T:T,F27,'MARGEN BRUTO'!U:U,F28,'MARGEN BRUTO'!W:W,F29,'MARGEN BRUTO'!V:V,F30,'MARGEN BRUTO'!S:S,F31)

Regards
Bosco

 

[Peter Bartholomew]

This treats each 16x12 table as a nested table requiring 2 category indices to define the overall row and column index
= INDEX( TABLE, 4*(CLIENTES.IDX-1)+TAMAÑO.IDX, 3*(TIPO.IDX-1)+COMPLEJIDAD.IDX )