Searching column "B" then report the value in the adjacent cell in column "A"

[Kindlysinful]

I have two columns of data, A and B
What I do is find the max number in column B, report that value in C3. Easy enough with =MAX(b2:b5000).
What I need to do next is find what 80% of that value is then search “B” for a number equal to or just above that 80% value. Then reference the adjacent cell in “A” and report the value in column "A" in C4.

 

[aaronbens]

If you can add a column of data then the following should work:
If your data set is A2:B5000, as noted C3 = MAX(B2:B5000)
In cell D2 you can add the formula =IF(B2-($C$3*0.8)<0,"",B2-($C$3*0.8)) can fill down to D5000.
C4 =INDEX(A2:A5000,MATCH(MIN(D2:D5000),D2:D5000,0))

 

[Kindlysinful]

Not having much luck. It is returning a -1.

 

[aaronbens]

Can you post a sample of the dataset and what the output is based on the set you provide?

 

[Kindlysinful]

So sorry for the extended delayed response, I was called out of the country for work. I will try and post an example but I was not seeing any borders.

 

[Kindlysinful]

[TABLE="width: 463"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Time (ms)[/TD]
[TD]Pressure[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.030[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.025[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD="align: right"]685.66781[/TD]
[TD]Max Psi[/TD]
[/TR]
[TR]
[TD="align: right"]-0.020[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Time to 80% Peak[/TD]
[/TR]
[TR]
[TD="align: right"]-0.015[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.010[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]-0.005[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.005[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.010[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.015[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.020[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.025[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.030[/TD]
[TD="align: right"]4.882812[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.035[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.040[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.045[/TD]
[TD="align: right"]4.882812[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.050[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.055[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.060[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.065[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.070[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.075[/TD]
[TD="align: right"]7.324219[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.080[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.085[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.090[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.095[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.100[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.105[/TD]
[TD="align: right"]7.324219[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.110[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.115[/TD]
[TD="align: right"]4.882812[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.120[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.125[/TD]
[TD="align: right"]7.324219[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.130[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.135[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.140[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.145[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.150[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.155[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.160[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.165[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.170[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.175[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.180[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.185[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.190[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.195[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.200[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.205[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.210[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.215[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.220[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.225[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.230[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.235[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.240[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.245[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.250[/TD]
[TD="align: right"]4.882812[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.255[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.260[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.265[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.270[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.275[/TD]
[TD="align: right"]4.272461[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.280[/TD]
[TD="align: right"]5.493164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.285[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.290[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.295[/TD]
[TD="align: right"]4.882812[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.300[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.305[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.310[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.315[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.320[/TD]
[TD="align: right"]6.103516[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.325[/TD]
[TD="align: right"]6.713867[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.330[/TD]
[TD="align: right"]10.0001[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.335[/TD]
[TD="align: right"]12.3242[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.340[/TD]
[TD="align: right"]16.9087[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.345[/TD]
[TD="align: right"]100.7653[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.350[/TD]
[TD="align: right"]104.2196[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.355[/TD]
[TD="align: right"]131.90761[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.360[/TD]
[TD="align: right"]159.59562[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.365[/TD]
[TD="align: right"]187.28363[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.370[/TD]
[TD="align: right"]214.97164[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.375[/TD]
[TD="align: right"]242.65965[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.380[/TD]
[TD="align: right"]270.34766[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.385[/TD]
[TD="align: right"]298.03567[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.390[/TD]
[TD="align: right"]325.72368[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.395[/TD]
[TD="align: right"]353.41169[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.400[/TD]
[TD="align: right"]381.0997[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.405[/TD]
[TD="align: right"]408.78771[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.410[/TD]
[TD="align: right"]436.47572[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.415[/TD]
[TD="align: right"]464.16373[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.420[/TD]
[TD="align: right"]491.85174[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.425[/TD]
[TD="align: right"]519.53975[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.430[/TD]
[TD="align: right"]547.22776[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.435[/TD]
[TD="align: right"]574.91577[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.440[/TD]
[TD="align: right"]602.60378[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.445[/TD]
[TD="align: right"]630.29179[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.450[/TD]
[TD="align: right"]657.9798[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0.455[/TD]
[TD="align: right"]685.66781[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]