Excel - Find the document of a given type, with the closest due date

Question

I am operating on 2 Tables.

"Table 1" is a list of current documents that I import and want to keep unchanged. The file is unformatted in any way.

Table 2 is a list of customers that I operate upon and is an excel table.

Now what I am trying to do is to create a formula in Table 2 that for each customer will look up his invoices in Table 1 and choose one that is closest to their respective due date (or in other way, the invoice which has a due date closest to today).

What I tried was:

Column F - Due dates
Column A - Customer number
Column C - Document type 

{=MIN(IF([Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY();[Documents.XLSX]Sheet1!$F$2:$F$30000))}

Which gave me the overall closest date which was one day away, but it was taken from all documents from all clients, which is not satisfactory.

I than tried adding a second conditional wanting to filter out only the invoices

{=MIN(IF(AND([Documents.XLSX]Sheet1!$C$2:$C$30000="INVOICE";[Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY());[Documents.XLSX]Sheet1!$F$2:$F$30000))}

Than things started getting wonky unfortunately.

Of course I could also throw in a third conditional with customer numbers, but that still wouldn't work.

{=MIN(IF(AND([Documents.XLSX]Sheet1!$C$2:$C$30000="INVOICE";[Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY();[Documents.XLSX]Sheet1!$F$2:$F$30000=[@CustomerNumber]);[Documents.XLSX]Sheet1!$F$2:$F$30000))}

I also tried to work around with a function in a shape of something akin to this:

=INDEX(AD3:AD9;MATCH(1;INDEX((AG3=AA3:AA9)*(AG4=AB3:AB9)*(AG5=AC3:AC9);0;1);0))

In which AD are values AG are inputs, AA AB and AC are different arrays for conditions (like document types, customer name etc.) As long as first two conditions can look like AG3 and AG4, I presume that the last condition has to be formulated differently in a manner in which the input data range is also the range for output.

Sample: https://i.stack.imgur.com/S8HCs.png

Now in this example the formula for Bob should return the value of 30.10.2018 as it is the closest invoice of today. While for John it should not return anything, as his invoice is already overdue.

EDIT: In the end I managed to figure it out on my own. Posting the resulting formula for anyone who might stumble upon this topic in the future:

{=MIN(IF(([Documents.XLSX]Sheet1!$C$2:$C$30000="Invoice")*([Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY())*(TEXT([@Customer];0)=[Documents.XLSX]Sheet1!$A$2:$A$30000);[Documents.XLSX]Sheet1!$F$2:$F$30000))}

This is an array formula so of course ctrl+shift+enter

If however anyone has an idea how to get simmilar results with the index-match combination, I would be more than happy to learn.

Answer 1

You can add an intermediary pivot table with ...

• the invoice numbers in the "row labels"
• the due dates in the "values" area.

Make sure to set the value field settings to "Max" so that for each invoice number you will extract the latest due date.

Then you can look those up via a "vlookup" in your table of interest.