0

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.

  • is $C$2:$C$30000 contents is the same as AD3:AD9 data? Coz, the reasoning (of both formula) seems hard to relate.. || I think sharing some sample data will to help to clarify the case.. – p._phidot_ Oct 26 '18 at 10:27
  • No it is not. The second function I have found on the internet, but had no idea how to really apply it to the data. exceljet.net/formula/index-and-match-with-multiple-criteria – Marcin Oct 26 '18 at 10:58
  • Welcome to Super User, and thanks for closing the loop on your question. The site relies on question posts containing just the question, and solutions being in answer posts. Rather than including your solution in the question, can you split that out into an answer. You will then also be able to indicate that the problem is solved by accepting your own answer (which you can do 2 days after posting the question by clicking the checkmark next to it). – fixer1234 Oct 27 '18 at 4:43
  • Would you mind sharing, some sample data? coz we cannot see where/which is your input row/column and the target output column/row of your files.. || you may use google drive or onedrive. || you may rename your column or re-enter the data to make it anonymous. || Just to be straight.. I think I get your question.. I just didn't get the case details.. – p._phidot_ Oct 28 '18 at 6:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.