Snasui Excel Forum

Hi dear I need your to fetch value from the table.Equal or greater than 16000 in column Is-3
It mean 16200 column k or Is-3
E.g. =round(15000*103%,-2)
=15500

=Round(15500*103%,-2)
=16000

Both value in column IS-2 or column k

Now C7 = equal or greater than 16000 in column L or IS-3



Best Wishes

Hi Guy,

Which table or cell(s) range that needed to place the formula?

Please place the possible results for us to understand better for what is the expected value to be resolved.

Norkaz

The problem when input 16000 in C2
It should return 16200 in C4 from the range J2:V42
The formula also attached

Hi Guy,

Refer to the formula as attached,


=LOOKUP((IF(MONTH(B2)<=6,ROUND((ROUND(C2*3%,-2)+C2)*0.03,-2)+(ROUND(C2*3%,-2)+C2),ROUND(C2*3%,-2)+C2)),INDEX($J$2:$V$42,,MATCH(D4,$J$1:$V$1,0)),INDEX($J$3:$V$43,,MATCH(D4,$J$1:$V$1,0)))

the result should be correct as appeared because ROUND(C2*3%,-2)+C2 became the number that greater than C2.

However, if you need the result by the data range J2:V42 reference, my suggestion is should focus just only the range table, then the trial formula should be like that.


C4

=AGGREGATE(15,6,INDEX($J$2:$V$42,0,MATCH(D4,$J$1:$V$1,0))/(INDEX($J$2:$V$42,0,MATCH(D4,$J$1:$V$1,0))>=C2),1)

Let us know, if any suggestion or something off the point or not connected with your requirement.

Norkaz

Thank you