Need help - cant figure out the right formula

[Optycal]

Hi, I'm having Excel issues and I'm stranded after multiple hours of googling.

Question:
How can I perform a VLOOKUP when the value is included in several rows (I want the highest number)

Context:
I have one overview where I wish list to various type of Information. This information have certain numerical criterias, which i named "LEVEL A", "LEVEL B" and "LEVEL C"
NOTE: The main thing here is that I have a Data-validation on "Included in System", which makes me get a drop-down list selection of various sheets. I have also added a VBA which make it possible for me to Select more than one item from the drop-down list.
The Second overview is the system, and I want the system to inherit the highest value from the Information sheet
--> e.g.: Information #2 is included in System #1 , #2 and #3 . How do i then get the System overview to show the highest value from the Information Valuation sheet?
--> I tried doing a MAX formula combined With VLOOKUP, but this only grants me the highest number of the first information that includes the specific system.

So to summary, I have this data:

​

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Information[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Included in system[/TD]
[/TR]
[TR]
[TD]Information1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]System1[/TD]
[/TR]
[TR]
[TD]Information2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]System1, System2, System3 [/TD]
[/TR]
[TR]
[TD]Information3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]System3[/TD]
[/TR]
</tbody>[/TABLE]


Which formula can I use to get this data?

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]System[/TD]
[TD]A (Max)[/TD]
[TD]B (Max)[/TD]
[TD]C (Max)[/TD]
[/TR]
[TR]
[TD]System1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]System2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]System3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

See if this does what you want.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Information	A	B	C	Included in system
2	Information1	1	2	3	System1
3	Information2	2	2	2	System1, System2, System3
4	Information3	4	4	4	System3
5
6		A	B	C
7	System1	2	2	3
8	System2	2	2	2
9	System3	4	4	4
Sheet

 

[Peter_SSs]

Another (standard-entry) possibility to try

Excel Workbook
	A	B	C	D	E
1	Information	A	B	C	Included in system
2	Information1	1	2	3	System1
3	Information2	2	2	2	System1, System2, System3
4	Information3	4	4	4	System3
5
6		A	B	C
7	System1	2	2	3
8	System2	2	2	2
9	System3	4	4	4
Max

 

[Optycal]

Thank you both for switf and good responses. Ended up using the Aggregate-formula, which worked perfectly. Thanks again!

 

[Peter_SSs]

Thank you both for switf and good responses.

You're welcome. Thanks for the follow-up.