sum in multiple sheets with corresponding values in excel

Question

I am new at Excel. I have 3 sheets: Number, Size and Time. The sheet Number is as below:

Name	Time
B1	20
B5	10
B12	40
S1	50
S2	70
T2	80

The sheet Size is a contingency table with combined of values from the 1st column of the sheet Number (I am lazy to fill in all the combined but the left cells have the same principe as combined values):

	ABC	BHG	XYZ	KLM	RTY	VBN
ABC	B1B12	B5S1
BHG	S2T2	T2S2
XYZ	B1B5B12	T2S2B5
KLM	B1B12S1	B5B12
RTY	T2S2S1	S1S2
VBN	T2T2S1	S1T2

Now, I expect to create the third sheet - Time which is similar to the sheet Size but the combined values are replaced by the sum of the corresponding values in the sheet Number. For example:

	ABC	BHG	XYZ	KLM	RTY	VBN
ABC	60	60
BHG	150	150
XYZ	70	160
KLM	110	50
RTY	200	120
VBN	210	130

The combined values of ABC and ABC is : B1 + B12 = 60 All left cells are calculated as the same way.

Does anyone know how to do it in Excel ?

I have tried Vlookup but as a beginner, I do not know where are the errors.

Answer 1

Here is one way of doing it, it is assumed that there is no Excel Constraints as per the tags posted, formulas used exclusively available for MS365 users:

---

=LET(
     _Size, VLOOKUP($A2,Size!$A$2:$G$7,XMATCH(Time!B$1,Size!$A$1:$G$1),0),
     _SplitAlpha, TEXTSPLIT(_Size,,SEQUENCE(10)-1,1),
     _SplitNum, TEXTSPLIT(_Size,,CHAR(SEQUENCE(26,,65)),1),
     IFERROR(SUM(VLOOKUP(_SplitAlpha&_SplitNum,Number!$A$2:$B$7,2,0)),""))

---