return value based on matrix column name and row name

[wulp2]

Hi,

There are a lot of INDEX/MATCH/VLOOKUP examples out there but I could not find the one that I need.

I want the following:
I have 2 tables,

table 1:
[TABLE="width: 169"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Code[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

and table 2 (the # and the @ are normally not there, it is for this example):
[TABLE="width: 169"]
<tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD][/TD]
[TD]Code[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 169"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]#[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]@[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]C [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I need to have a formula in table 2 to find the value in the totals column in table 1 by using the colum names and the row names in table 2.
In this example, a 5 should be where the # is (there is a C2 in table 1) and if there is no simularity in table 1 it should not return anything like for example the @ (there is no A2 in table 1).

I hope I am clear enough for someone to help me out.

Regards,
Raoul

 

[AlanY]

try this

Book1
	A	B	C
1	Project	Code	Totals
2	C	1	10
3	C	2	5
4	C	3	0
5	D	2	7
6
7
8	Project	1	2
9	C	10	5
10	A
11	D		7
Sheet1

Cell Formulas
Range	Formula
B9	{=IFERROR(INDEX($C$2:$C$5,MATCH(B$8,IF($A$2:$A$5=$A9,$B$2:$B$5),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James006]

Hi,

Not sure to fully understand your structure ...

With the number 2 of your table 2 located in cell C11 ...

do you need

=INDEX($C$2:$C$5,C11)

Hope this will help

 

[wulp2]

BOTH, thank you for the quick response, well received.
@AlanY, that would indeed help, I however do not want to use an array formula as table 2 changes in size. @James006, the example of AlanY shows EXACTLY what I want, he also has an answer but I do not want to use an array if remotely possible.

I could also use a helper column to merge column A & B (=A2&B2) but I would like to do without a helper column.

 

[wulp2]

Ah, wait, it is not a problem if copied. Thx!, I can work with this!

 

[AlanY]

you can use sumproduct instead without the arrays formula like this

Book1
	A	B	C	D	E	F	G	H
1	Project	Code	Totals		Project	1	2	3
2	C	1	10		C	10	5	0
3	C	2	5		A	0	0	8
4	C	3	0		D	0	7	0
5	D	2	7
6	A	3	8
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(($A$2:$A$1500=$E2)*($B$2:$B$1500=F$1),$C$2:$C$1500)