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

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
719
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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
4567
9101215
14151823
19202431
24253039target = 2727
30303647
34354255clearly 29 is nearest above
39402963
44455471
find the positive distance from target
4
312
33920
781528
1213236
17182744
min of above table2
add 2729

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

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top