Require OUTPUT basis 2 figures in INPUT & given Matrix

[RavindraK]

Dear Excel experts,
We have a matrix for equipment deployment for 3 different conditions in ideal scenario. And accordingly we require OUTPUT considering 2 provided figures and given matrix.

If, the INPUT figure is lies between 2 figures, OUTPUT figure is to be calculated proportionately considering provided 2 figures in INPUT and above MATRIX.
For Example,
QCs = 5 & No of Trucks inside the yard are 350, No of RTGs should be considered in between 28 & 32 considering same ratio between 4 & 6

If, the INPUT figure is beyond the maximum as per provided MATRIX, OUTPUT figure is to be calculated basis MAXIMUM figure only.
For Example,
QCs = 9 & No of Trucks inside the yard are 450, No of RTGs should be considered 37 not beyond that.

Please help me to resolve the same.

Thanks in advance.

RavindraK

[RavindraK]

OUTPUT single figure is require basis..
updated INPUT (two figures) and given matrix

[JeteMc]

This proposal employs a helper table (C29:I41) which may be moved down the sheet and/or hidden for aesthetic purposes.
D30:I30 is populated using: =INDEX($D3:$I3,COUNTA($D3:$I3)+COLUMN($D3:$I3)-COLUMN())
D32:I41 is populated using: =IFERROR(INDEX($D$5:$I$10,MATCH($C32,$B$5:$B$10,0),MATCH(D$30,$D$3:$I$3,0)),ROUNDUP(SUM(D31,D33)/2,0))
The formula for the number of RTG's is: =INDEX(D32:I41,MATCH(D14,C32:C41),MATCH(D15,D30:I30))
The formula for the number of QC's is: =INDEX(C32:C41,AGGREGATE(14,6,(ROW(C32:C41)-ROW(C31))/(D30:I30<=H14)/(INDEX(D32:I41,,MATCH(H14,D30:I30))=H15),1))
The formula for the number of trucks inside the yard is: =INDEX(D30:I30,AGGREGATE(14,6,(COLUMN(D30:I30)-COLUMN(C30))/(D32:I41=L15)/(C32:C41>=L14),1))
Let us know if you have any questions.

[RavindraK]

Thanks a lot Sir,
It almost done except a minor belwo discripancy..
Maximum figures are defined even after beyond maximum as per matrix
However, the figures below minimum is not capturing hare, like after puting INPUT as No of Trucks inside the yard below 225 it's not working and as per requirement it should consider only 225 even input is below 225 i.e. 200, 190, 100, 10 etc., but it should consider only 225 as minimum.
Please help.
Thanks in advance.

RavindraK

[JeteMc]

See if pasting the following into D18 helps:

=IF(D15<D30,INDEX(D32:D41,MATCH(D14,C32:C41)),INDEX(D32:I41,MATCH(D14,C32:C41),MATCH(D15,D30:I30)))

Let us know if you have any questions.

[RavindraK]

Great Sir,
It's done as expected, you are really a great excel master !!
hats off to you Sir

Thanks a lot again.

RavindraK

[JeteMc]

You're Welcome and Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.