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