CODEWITHSUNDEEP
arraysexcelif-statementexcel-formulamatch
Matt9080
Matt9080

Reputation: 45

Issue with Arrays/Match/Index

Here is my current formula I use to pull the latest live date for a customer out of a web query.

{=LARGE(
    IF(Table_owssvr_1[HQ Name]=B1,1,0)*
    IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0),
  1)}

B1 is the name of the customer. HQ name is column A and contains customer names.

This formula will give me the latest open date for the customer but it doesn't filter whether it was a live, closed, or customer on hold. The problem I am having is that I need to have this formula check one more column in that table to see that is matches Live -3 before giving me the largest date. It is column C in the table Status. If the data in the column says Live -3 for that customer, then I need it to pull the largest date from among multiple entries that say the Live -3. The dates are in column L, Live Date.

I've been attempting using match and index function but have had no luck. I appreciate any help.

Upvotes: 2

Views: 110

Answers (1)

Byron Wall
Byron Wall

Reputation: 4010

This should be as simple as multiplying in another condition that returns 1/0. You will get a 0 unless both conditions are met.

{=LARGE(
    IF(Table_owssvr_1[HQ Name]=B1,1,0)*
    IF(Table_owssvr_1[Status]="Live -3",1,0)*
    IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0),
  1)}

Upvotes: 1

Related Questions