Lookup Formula

[p9j123]

I need help on a lookup formula, if possible not VBA.

Here is the requirements:

On Sheet 1 If I put 5% on A1 and PR on B1, then C1 should be return 0.26, this should be based on the table on sheet 2 as shown below.

Pages Split %age	CD	PR	QC	LT
0%	0.27	0.24	0.15	0.36
5%	0.30	0.26	0.16	0.39
10%	0.32	0.28	0.17	0.43
15%	0.34	0.30	0.18	0.46
20%	0.37	0.33	0.19	0.49
25%	0.39	0.35	0.20	0.52
30%	0.41	0.37	0.21	0.55
35%	0.44	0.39	0.23	0.58
40%	0.46	0.42	0.24	0.61
45%	0.48	0.44	0.25	0.64
50%	0.51	0.46	0.26	0.67

<tbody>
</tbody>

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E
1	5%	PR	0.26
2
3	Pages	CD	PR	QC	LT
4	0%	0.27	0.24	0.15	0.36
5	5%	0.3	0.26	0.16	0.39
6	10%	0.32	0.28	0.17	0.43
7	15%	0.34	0.3	0.18	0.46
8	20%	0.37	0.33	0.19	0.49
9	25%	0.39	0.35	0.2	0.52
10	30%	0.41	0.37	0.21	0.55
11	35%	0.44	0.39	0.23	0.58
12	40%	0.46	0.42	0.24	0.61
13	45%	0.48	0.44	0.25	0.64
14	50%	0.51	0.46	0.26	0.67
Sheet3

Cell Formulas
Range	Formula
C1	=INDEX($B$4:$E$14,MATCH($A$1,$A$4:$A$14,0),MATCH($B$1,$B$3:$E$3,0))

 

[p9j123]

Thank you so much, it works perfectly.

try this

Excel 2012

	A	B	C	D	E
1	5%	PR	0.26
2
3	Pages	CD	PR	QC	LT
4	0%	0.27	0.24	0.15	0.36
5	5%	0.3	0.26	0.16	0.39
6	10%	0.32	0.28	0.17	0.43
7	15%	0.34	0.3	0.18	0.46
8	20%	0.37	0.33	0.19	0.49
9	25%	0.39	0.35	0.2	0.52
10	30%	0.41	0.37	0.21	0.55
11	35%	0.44	0.39	0.23	0.58
12	40%	0.46	0.42	0.24	0.61
13	45%	0.48	0.44	0.25	0.64
14	50%	0.51	0.46	0.26	0.67

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C1 =INDEX($B$4:$E$14,MATCH($A$1,$A$4:$A$14,0),MATCH($B$1,$B$3:$E$3,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

you're welcome