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

P/ORequiredProductSORT BY PO
P9074919-03-082241773
P9074919-03-082241773
P9075019-05-012241773
P9075719-03-311132113
P9075719-03-3111321141145879
P9117919-03-11114587919-03-17
P9118019-03-151145879#N/AClosest Match , SHOULD BE P91180
P9118119-04-021145879INDEX(A2:A20,MATCH(1,1*(E6=C2:C20)*(E7=B2:B20)*(MIN(IF(B2:B20>E7,B2:B20))),0))
P9118219-05-0111458791145879
P9118319-06-01114587919-05-01
P9119919-05-151120975P91182Exact Match
P9121719-03-201130397INDEX(A2:A20,MATCH(1,(F10=C2:C20)*(F11=B2:B20),0)
P9126119-03-081120976
P9139019-03-301130397
P9142719-03-271124940
P9149119-03-011140773
P9157219-03-231130882
P9161419-03-278229026
P9161519-05-018229026

<colgroup><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

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