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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
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,127,783
Messages
5,626,860
Members
416,207
Latest member
chouba

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