Match value and date range on separate Tabs

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
79
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,392
Office Version
  1. 2016
Platform
  1. Windows
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
BCDE
1VendorInvoice Date
2ACME01-Jan-20
3Whittle02-Feb-20
4Johnson02-Feb-17
5Peters09-Sep-20
6Pyles06-Jun-20
7Whittle02-Feb-20
8Johnson02-Feb-17
9Peters09-Sep-20
10ACME07-Jul-20
11Whittle02-Feb-20
12Johnson02-Feb-17
13Peters09-Sep-20
Invoices


Here's the Master sheet:

Gesyca_is_joy.xlsx
ABCDE
1VendorActive?As at:05-Feb-21
2ACMEYes
3WhittleInactive
4JohnsonInactive
5PetersYes
6Koddle 
7PylesYes
Master
Cell Formulas
RangeFormula
B2:B7B2=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"))
 
Solution

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
79
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,392
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,809
Messages
5,627,019
Members
416,215
Latest member
Ostie3994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top