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_ – 2018-10-26T10:27:52.850
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. https://exceljet.net/formula/index-and-match-with-multiple-criteria
– Marcin – 2018-10-26T10:58:14.890Welcome 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 – 2018-10-27T04:43:41.460
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_ – 2018-10-28T06:03:03.580