Help with a tricky formula? Lots of conditions?

New Contributor

Hi, I've attached an excel sheet that has been cleaned up and simplified. I need help with formulas in two cells if possible.


On the Metrics tab, I am looking for formulas for cells 7E and 7F (or maybe it could be one formula). There's a formula in 7F now that I have flubbed. This is what I'm trying to accomplish:


When a name is selected in cell 5E of the metrics tab, I would like the columns below to populate the following information (pulling from the "active inventory" tab):

  • I would like the number (column C in Active Inventory tab) of any matters associated with that name (column E in Active inventory tab) to populate
  • I would also like it to include their respective end dates (column I in Active Inventory) in the next cell
  • BUT I want to limit it to matters with an end date in the next 30 days (I have date formulas in column I of the Metrics spreadsheet). 

Is anyone able to help? Again, not sure if this can be done with the new XLookup formula, however, if that can be avoided that would be best because I need this to work in a gsheet as well.



3 Replies


Insert a row above row 1 in sheet Active Inventory. Type a 1 in C1 an I1.


Formula for E7:

=FILTER(FILTER('Active Inventory'!A3:P130,('Active Inventory'!E3:E130=E5)*('Active Inventory'!I3:I130<=TODAY()+30)*('Active Inventory'!I3:I130>=TODAY())),'Active Inventory'!A1:P1)



@Detlef Lewin this isn't working for me, I get an error: Error: No matches are found in FILTER evaluation.

Just kidding! I was one cell off! Thank you!!!!!!!!