Match value and date range on separate Tabs

[Gesyca_is_joy]

Hello, so I have a pretty monster excel workbook that tracks a TON of information and rocks some pretty sweet formulas in it, but due to the amount of data it is becoming overwhelming to track what needs attention and I'm making a dashboard tab (don't ask\y why this project is still in excel when by all rights it should be a database. just gonna go with budget cuts and leave that there)

So one of the dashboard items is to tell us how many active vendors we have at any one point in time. currently I do this by a formula resides on the Master Vendor tab that has a column which finds all the active vendors and either has a value of "yes" or blank. (the dashboard will count the number of "yes" values in a column.
this formula works by finding the vendor name in the Invoices tab and matching it to the vendor name in column A of the Vendor Master tab; if we have invoiced them then its a "Yes".
Formula: =IFERROR(IF(MATCH(A4,Invoices!B:B,0),"Yes",),"")

However, I am now supposed to expand this count to rule out any vendors that haven't invoiced us in the past year, I was thinking about taking the above formula and adding in a lookup to find the vendor, then check the INVOICE DATE column (Invoices!E:E) and if there are no dates less than 365 from today return the value "Inactive". I say it this way because you figure, some vendors we get invoices from each month, others not so much. this formula could very well find anywhere from 1-50 records that match the vendor name, if even one of those records has a date within the past 365 days then the vendor is considered active. only if EVERY record is older than 365 days are they inactive.

I can't use an INDEX MATCH because I'm not asking it to return an indexed value. The complete formula should ultimately return a "Yes", a "Inactive" or a blank cell. and I'll be honest, I suck at date formulas. I cannot get this thing to work.

 

[Toadstool]

Hi Gesyca_is_joy,

So if the Vendor isn't on the Invoices return null, if no invoices are later than today - 365 days then Inactive, if all invoices are older than today -365 days then Yes.

My forumale use all rows so you can get it to run faster if you can limit the row numbers.

I'm using a today date of cell E1 so you can change it to test. You can later change the $E$1 to TODAY()

Here's my Invoices sheet:

Gesyca_is_joy.xlsx
	B	C	D	E
1	Vendor			Invoice Date
2	ACME			01-Jan-20
3	Whittle			02-Feb-20
4	Johnson			02-Feb-17
5	Peters			09-Sep-20
6	Pyles			06-Jun-20
7	Whittle			02-Feb-20
8	Johnson			02-Feb-17
9	Peters			09-Sep-20
10	ACME			07-Jul-20
11	Whittle			02-Feb-20
12	Johnson			02-Feb-17
13	Peters			09-Sep-20
Invoices

Here's the Master sheet:

Gesyca_is_joy.xlsx
	A	B	C	D	E
1	Vendor	Active?		As at:	05-Feb-21
2	ACME	Yes
3	Whittle	Inactive
4	Johnson	Inactive
5	Peters	Yes
6	Koddle
7	Pyles	Yes
Master

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISNA(MATCH(A2,Invoices!$B:$B,0)),"",IF(ISNUMBER(MATCH(1,INDEX((Invoices!$B:$B=A2)*(Invoices!$E:$E>Master!$E$1-365),0),0)),"Yes","Inactive"))

 

[Gesyca_is_joy]

Thank you very much, that works like a charm. I sincerely appreciate it!!

I updated the columns in the formula to include the defined names (I use tables to limit the calculations so they don't try and consider all rows/columns. and inserted a "Missing Vendor ID" check because that is a thing in my data as well. Here is the final product:

=IF(ISBLANK([@ID]),"Vendor ID Missing",IF(ISNA(MATCH([@ID],InvoiceLog[VENDOR ID],0)),"",IF(ISNUMBER(MATCH(1,INDEX((InvoiceLog[VENDOR ID]=[@ID])*(InvoiceLog[INVOICED]>TODAY()-365),0),0)),"Yes","Inactive")))


Can you please walk me through the date logic? I get the Match formula at the beginning but the calculating dates part is where I always mess up and I'd like to learn to get better.

 

[Toadstool]

Let me try and explain:

IF(ISNA(MATCH([@ID],InvoiceLog[VENDOR ID],0)),""
If there's no match then return null

IF(ISNUMBER(
I'm going to look for the row where an invoice for this vendor with a date greater than today-365 exists so ISNUMBER will give a TRUE if one is found and a FALSE if not. So I'll look for an invoice date 6 Feb 2020.
i.e. 5 Feb 2021 - 365 = 6 Feb 2020

The latest invoice for ACME is 7 Jul 2020 so I get the row number for that first invoice after 6 Feb 2020 (in this case row 10) which satisfies the ISNUMBER so IF returns TRUE and the formula returns Yes.
The latest invoice for Whittle is 2 Feb 2020 so no invoice is > 6 Feb 2020 so the ISNUMBER returns false and I get Inactive.

MATCH(1,INDEX((InvoiceLog[VENDOR ID]=[@ID])*(InvoiceLog[INVOICED]>TODAY()-365),0),0)),"Yes","Inactive")))
The MATCH(1 looks for a logical 1 (TRUE) when INDEXing into a check of the vendor id and the invoice date later than 365 days ago. If either is FALSE then multiplied together they give a logical zero. If both are true they return a logical 1.
The MATCH therefore finds a match for its 1 when both are true and returns the row number.
If the MATCH finds no such row then it returns a #N/A which the ISNUMBER treats as false.