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

#### countryfan_nt

##### Well-known Member
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!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
 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>

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

Replies
2
Views
169
Replies
2
Views
394
Replies
1
Views
163
Replies
14
Views
867
Replies
5
Views
177

1,220,978
Messages
6,157,180
Members
451,402
Latest member
Lyv

### 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.

### Which adblocker are you using?

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

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