Looking for the highest closest match, based on multiple conditions.

[countryfan_nt]

Hello friends, hope all is well,

Please help me with the formula, I want the below formula last part to pickup the highest amount closest to L2.
I feel that the below formula is not doing it.

Here are the conditions:

Sheet Pa - Range: K3:k1029 = target K2
Sheet Pa - Range: E3:E1029 = target I2
Sheet Pa - Range: I3:I1029 = target L2.

Please note if it can’t find the value of L2, then find the closest highest amount form the range Range Pa: range I3:I1029.

=MAX(IF(‘Pa’!$K$3:$K$1029=tareget!K2,IF(‘Pa’!$E$3:$E$1029=tareget!I2,’Pa’!$I$3:$I$1029=L2)))

Thank you so much in advance!

 

[oldbrewer]

		4	5	6	7
		9	10	12	15
		14	15	18	23
		19	20	24	31
		24	25	30	39			target = 27	27
		30	30	36	47
		34	35	42	55			clearly 29 is nearest above
		39	40	29	63
		44	45	54	71
		find the positive distance from target
					4
				3	12
		3	3	9	20
		7	8	15	28
		12	13	2	36
		17	18	27	44
		min of above table	2
		add 27		29

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[countryfan_nt]

I apologize, but I did not get it, can you please help me with the formula?

 

[oldbrewer]

consider the first number 4 check if it is below 29 if it is return a blank if not return number minus 29

say 4 is in A1 and your formula is in A20 and the 27 is in A10

=if(A1<$a$10,"",$a$10-A1)

drag across and down

then find the smallest number in this grid