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: -

[TABLE="width: 438"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]PR[/TD]
[TD]<80[/TD]
[TD]81-90[/TD]
[TD]91-100[/TD]
[TD]101-110[/TD]
[TD]>110[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6.0%[/TD]
[TD]5.0%[/TD]
[TD]4.0%[/TD]
[TD]3.0%[/TD]
[TD]2.0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5.0%[/TD]
[TD]4.0%[/TD]
[TD]3.0%[/TD]
[TD]2.0%[/TD]
[TD]1.0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4.0%[/TD]
[TD]3.0%[/TD]
[TD]2.0%[/TD]
[TD]1.0%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.0%[/TD]
[TD]2.0%[/TD]
[TD]1.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[/TR]
</tbody>[/TABLE]


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
PR up to

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Return[/TD]
[TD="align: center"][/TD]
[TD="align: center"]AH[/TD]
[TD="align: center"][/TD]
[TD="align: center"]AQ[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3.00%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]80%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]110%[/TD]
[TD="align: right"]999%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

soph12081​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]