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]

I was over-thinking it before, try

=MAX(LARGE(A6:X6,2),LARGE(Y6:AM6,2))

 

[tamsh]

I was over-thinking it before, try

=MAX(LARGE(A6:X6,2),LARGE(Y6:AM6,2))

I need it to return from full array next biggest number that is not the two already used, so not sure this would work as I think this returns 2nd largest in each, where there could be same score for multiples.

 

[Fluff]

What should happen if you have two scores of 45 in Y6:AM6?

 

[tamsh]

What should happen if you have two scores of 45 in Y6:AM6?

So I have found the highest in A6:X6 and the highest in Y6:AM6. Now I need the next highest in A6:AM6, even if equal to either of other highest just as long as not specifically those so I am not repeating, in effect I need the highest individual sport score, highest team sport score and then next highest in either.

If scores are all 25/25 then I would get the first 25 in A6:X6, the first 25 in A6:AM6 then a third result of 25 from A6:AM6 as long as wasnt one of the two already returned, hence why trying to use cell references.

Maybe I am over complicating it...

 

[Fluff]

In that case the formula I suggested should work.

 

[Kerryx]

or thi9s maybe, not sure

Book1
	A	B	C	D
1	1	2	3	3
2	8	8	9	9
3				8
Sheet2

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

 

[tamsh]

or thi9s maybe, not sure

Book1
	A	B	C	D
1	1	2	3	3
2	8	8	9	9
3				8
Sheet2

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

Thanks so much

 

[tamsh]

In that case the formula I suggested should work.

Thanks so much

 

[Fluff]

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)

 

[tamsh]

In that case the formula I suggested should work.

Thanks so much