Help - Multiple Criterias using a range

[soph12081]

Hello

I think I have over analysed this and I think it's probably simple but any help would be appreciated.

Basically I need my data table to pull a figure into column AJ "% increase" based on the % in column AQ "PIR" and the PR number in column AH.

An example: -

AQ2=99.43%
AH2=2

It should return 1%.

Data table looks like this: -

PR	<80	81-90	91-100	101-110	>110
5	6.0%	5.0%	4.0%	3.0%	2.0%
4	5.0%	4.0%	3.0%	2.0%	1.0%
3	4.0%	3.0%	2.0%	1.0%	0.0%
2	3.0%	2.0%	1.0%	0.0%	0.0%
1	0.0%	0.0%	0.0%	0.0%	0.0%

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

Thanks in advance!

 

[Toadstool]

I had to change your text headings for PR to a single maximum for that range, then this would work:

	A	B	C	D	E	F	G	AG	AH	AI	AQ
1							Return		AH		AQ
2							3.00%		2		3.00%
3
4	PR up to	80%	90%	100%	110%	999%
5	5	6.00%	5.00%	4.00%	3.00%	2.00%
6	4	5.00%	4.00%	3.00%	2.00%	1.00%
7	3	4.00%	3.00%	2.00%	1.00%	0.00%
8	2	3.00%	2.00%	1.00%	0.00%	0.00%
9	1	0.00%	0.00%	0.00%	0.00%	0.00%

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

soph12081​

Worksheet Formulas Cell Formula G2 =INDEX($B$5:$F$9,MATCH(AH2,$A$5:$A$9,0),AGGREGATE(15,6,COLUMN($B$4:$F$4)-1/(AQ2<=$B$4:$F$4),1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>