Something like SUMPRODUCT but outputting text?

[hugoexcel22]

Good morning,

I'm making a report with a summary sheet where the data must be displayed, I already made 1 column that works perfectly fine and other one that is not working properly.

I have this formula:

=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")

For other cells I would have to add this also:

=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$C:$C;C2;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")

I also would need it to read not only the "CRITICAL" but also if it contains MEDIUM-HIGH, MEDIUM, LOW and IG, but I don't know how to add that (is not even working now so...)

That returns a "SPILL", what I need is the following:

PCI PENDING GAPS.xlsx
	A	B	C	D	E
1	THING 1	THING 2	ASSET	SERVICE	CURRENT STATUS
2	THING A	THING A2	-	-	#SPILL!
3	THING B	THING B2	ASSET1	SERVICE1	#SPILL!
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,STATUS!A2,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,STATUS!B2,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND")
E3	E3	=IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,A3,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,B3,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND")

Named Ranges
Name	Refers To	Cells
VULNERABILITIES!_FilterDatabase	=VULNERABILITIES!$A$1:$N$190	E2:E3

This is sheet 1 where I have to put the formula because it is the summary, and this is sheet 2 where the data is taken:

PCI PENDING GAPS.xlsx
	A	B	C	D	E	F
1	THING 1	THING 2	ASSET	SERVICE	SEVERITY	STATUS
2	THING A	THING A2	-	-	CRITICAL	UNRESOLVED
3	THING B	THING B2	ASSET1	SERVICE1	MEDIUM-HIGH	UNRESOLVED
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:F3	Cell Value	contains "UNRESOLVED"	text	NO
F1:F3	Cell Value	contains "PENDING"	text	NO
F1:F3	Cell Value	contains "PPVV"	text	NO
F1:F3	Cell Value	contains "SOLVED"	text	NO
E1:E3	Cell Value	contains "CRITICAL"	text	NO
E1:E3	Cell Value	contains "MEDIUM-HIGH"	text	NO
E1:E3	Cell Value	contains "MEDIUM"	text	NO
E1:E3	Cell Value	contains "LOW"	text	NO
E1:E3	Cell Value	contains "IG"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	CRITICAL;MEDIUM-HIGH;MEDIUM;LOW;IG
F2:F3	List	UNRESOLVED;SOLVED;PPVV;PENDING

Basically I need to show on Sheet1 the summary of the data from Sheet2, I have 2 main values: Thing A and Thing B, but Thing A can be linked to Thing B or C, the status of the Thing can be 4: Unresolved, Resolved, PPVV and Pending, which also can have 5 possible severity results CRITICAL, MEDIUM-HIGH, MEDIUM, LOW, IG. What I need to show on Sheet1 is the most serious, I have used the following formulas for the other column that does not appear and it works perfectly:

=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))

=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!C:C;C3;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))

What I need is something similar but output the specified text based on existing unresolved rows.

If I'm not being clear please tell me.

Regards.

 

[hugoexcel22]

Nevermind, solved.

 

[Fluff]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.