Excel formula to compare columns with one column and row

[AniSaikia]

Hi,
Could you help with the formula for below
Sheet 1 has suppose
process Name
A Test
B Test1
C. Test2

Sheet 2 has
Row1
process. Test. Test1 Test 2
A. 10. 20. 30
B. 20. 90. 5
C. 5. 6. 4

So if sheet1 process A = sheet 2 process A and Sheet 1 Test = sheet 2 Test, it should return value as 10 ...Please suggest

Thanks in advance

 

[Jonmo1]

Re: Excel formual to compare columns with one column and row

Welcome to the board
Try
=INDEX(Sheet2!$B$2:$D$4,MATCH(A2,Sheet2!$A$2:$A$4,0),MATCH(B2,Sheet2!$B$1:$D$1,0))

Sheet1

Book1
	A	B	C
1	Process	Name	Formula
2	A	Test	10
3	B	Test1	90
4	C	Test2	4
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX(Sheet2!$B$2:$D$4,MATCH(A2,Sheet2!$A$2:$A$4,0),MATCH(B2,Sheet2!$B$1:$D$1,0))
C3	=INDEX(Sheet2!$B$2:$D$4,MATCH(A3,Sheet2!$A$2:$A$4,0),MATCH(B3,Sheet2!$B$1:$D$1,0))
C4	=INDEX(Sheet2!$B$2:$D$4,MATCH(A4,Sheet2!$A$2:$A$4,0),MATCH(B4,Sheet2!$B$1:$D$1,0))

Sheet2

Book1
	A	B	C	D
1	Process	Test	Test1	Test2
2	A	10	20	30
3	B	20	90	5
4	C	5	6	4
Sheet2

 

[AniSaikia]

Re: Excel formual to compare columns with one column and row

Thank you

 

[Jonmo1]

Re: Excel formual to compare columns with one column and row

You're welcome.