Index-match return exact match, if not closest match

ALVG2019

New Member
Joined
Mar 8, 2019
Messages
1
I have this formula : INDEX(A2:A20,MATCH(1,(F10=C2:C20)*(F11=B2:B20),0)
It returns the exact match. I need the formula to return the closest match if the there's no exact match

this is my data : A2:A20
I need to find the PO that is closest to the date 19-03-17 , the result should be : P91180

[TABLE="width: 795"]
<colgroup><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]P/O[/TD]
[TD]Required[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD]SORT BY PO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P90749[/TD]
[TD]19-03-08[/TD]
[TD]2241773[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P90749[/TD]
[TD]19-03-08[/TD]
[TD]2241773[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P90750[/TD]
[TD]19-05-01[/TD]
[TD]2241773[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P90757[/TD]
[TD]19-03-31[/TD]
[TD]1132113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P90757[/TD]
[TD]19-03-31[/TD]
[TD]1132114[/TD]
[TD][/TD]
[TD]1145879[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91179[/TD]
[TD]19-03-11[/TD]
[TD]1145879[/TD]
[TD][/TD]
[TD]19-03-17[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91180[/TD]
[TD]19-03-15[/TD]
[TD]1145879[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]Closest Match , SHOULD BE P91180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91181[/TD]
[TD]19-04-02[/TD]
[TD]1145879[/TD]
[TD][/TD]
[TD="colspan: 5"]INDEX(A2:A20,MATCH(1,1*(E6=C2:C20)*(E7=B2:B20)*(MIN(IF(B2:B20>E7,B2:B20))),0))[/TD]
[/TR]
[TR]
[TD]P91182[/TD]
[TD]19-05-01[/TD]
[TD]1145879[/TD]
[TD][/TD]
[TD]1145879[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91183[/TD]
[TD]19-06-01[/TD]
[TD]1145879[/TD]
[TD][/TD]
[TD]19-05-01[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91199[/TD]
[TD]19-05-15[/TD]
[TD]1120975[/TD]
[TD][/TD]
[TD]P91182[/TD]
[TD]Exact Match[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91217[/TD]
[TD]19-03-20[/TD]
[TD]1130397[/TD]
[TD][/TD]
[TD="colspan: 2"]INDEX(A2:A20,MATCH(1,(F10=C2:C20)*(F11=B2:B20),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91261[/TD]
[TD]19-03-08[/TD]
[TD]1120976[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91390[/TD]
[TD]19-03-30[/TD]
[TD]1130397[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91427[/TD]
[TD]19-03-27[/TD]
[TD]1124940[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91491[/TD]
[TD]19-03-01[/TD]
[TD]1140773[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91572[/TD]
[TD]19-03-23[/TD]
[TD]1130882[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91614[/TD]
[TD]19-03-27[/TD]
[TD]8229026[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P91615[/TD]
[TD]19-05-01[/TD]
[TD]8229026[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Maybe this array formula that must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEF
1P/ORequiredProductDatePO
2P907493/8/201922417733/17/2019P91180
3P907493/8/20192241773
4P907505/1/20192241773
5P907573/31/20191132113
6P907573/31/20191132114
7P911793/11/20191145879
8P911803/15/20191145879
9P911814/2/20191145879
10P911825/1/20191145879
11P911836/1/20191145879
12P911995/15/20191120975
13P912173/20/20191130397
14P912613/8/20191120976
15P913903/30/20191130397
16P914273/27/20191124940
17P914913/1/20191140773
18P915723/23/20191130882
19P916143/27/20198229026
20P916155/1/20198229026
Sheet
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top