Max number if not equal to the cell references in two other cells

[tamsh]

Hi, I would like to return the max number from a list, if that number has already not been selected in two other cells. Thus far I have used max to find the highest number in one array, putting result into a cell. Then a second max to find the highest number in a second array, putting result into a cell. Then used CELL to display the cell reference of each maximum number in two other cells.

What I now need is to find the highest number in the entire array (both arrays) that has not already been identified in the other two max formulas.

Hope that makes sense...

Can this be done?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Kerryx]

The formula from Kerryx does not do what you said you wanted.
In this scenario you said it should return 9 which it doesn't.

Fluff (version 1).xlsb
	A	B	C	D
1	1	2	3	3
2	8	9	9	9
3				8
Main

Cell Formulas
Range	Formula
D1:D2	D1	=MAX(A1:C1)
D3	D3	=LARGE(IF(D1:D2<>A1:C2,A1:C2),1)

Maye just misunderstood whole question as i thought if for example 9 was already in the max selection in one of the previous cells , he was looking for next highest value from all the arrays, as 9 was already there it would be 8...

 

[Fluff]

That's not how read it, but I could easily be wrong.

 

[Kerryx]

That's not how read it, but I could easily be wrong.

Been there done that multiple times , this could be another one