Need help with a Vlookup IF Statement & Countifs formula

[Joey86]

Hi All,

I'm just needing help with putting together a Vlookup, IF Statement & Countifs formula. Basically I have the following sheets:-
- Customers (3 column table: Long Name, Acronym, Other Name)
- Incidents_Open (4 column table: Incident Number, Inc Organization, Sev, Type)
- Team (multiple columns with Customer Names on top as Acronym)

What I'm needing the formula to do is firstly do a lookup a lookup of the correct customer name by matching the customer name from the Team sheet with the name used in Inc Organization and if it can't find it, then use the Other Name in the Customers sheet. Then it needs to do a countifs like below:-
=COUNTIFS(Incidents_Open!J:J,"TeamABC",Incidents_Open!D:D,"Incident",Incidents_Open!B:B,Team!E9 ....... (where "E9" is the cell containing the Customer Name Acronym)

Hope that all makes sense, thanks so much if someone can help me sort this.

 

[alvin-chung]

Welcome to the forum!

Before proceeding with the formula, could you clarify following scenario?
--- If the [acronym] in "Team" couldn't be matched with [Inc Organization] in "Incidents_Open", try match it with [Other Name] in "Customers".
--- If found a match, get the [Acronym] in "Customers" and lookup [Inc Organization] in "Incidents_Open"?
--- In this case, what is the expected result if the [Acronym] in "Customers" has no match for [Inc Organization] in "Incidents_Open"?

 

[Joey86]

Hi Alvin and thanks so much for your prompt response

- In this scenario if the [acronym] in "Team" can't be matched with the [Inc Organization] in "Incidents_Open" then it should just use the [acronym] in "Team"
- Yes this scenario is correct
- In this scenario it should then use the [Long Name] in "Customers"

 

[alvin-chung]

Here's the formula for sharing

=IF(ISNA(VLOOKUP(B1,Incidents_Open!$B$2:B$4,1,FALSE)),IF(ISNA(VLOOKUP(B1,Customers!$A$2:$B$4,2,FALSE)),IF(ISNA(VLOOKUP(B1,Customers!$C$2:$D$4,2,FALSE)),0,"Scenario C: Acronym found in Other Name"),"Scenario B: Acronym found in Long Name"),"Scenario A: Acroynm found in Incidents_Open")