A Formula that looks up and displays the highest value over time

[Gmunro]

Hi all

I am looking for the correct formula that will look up a series of cells and always display the highest value. I have included an example that shows what I am looking for. In the spreadsheet block one will be filled in first and block 2 & 3 will be filled in over time. So I wish the 'Best' section to always be checking the contents of the cells and they are to be ignored if they are empty.

Example Outcome:

V3 would display the highest value from A3, H3 and O3.
W3 would display the highest value from B3, I3 and P3

So the best section will mirror block one data, but when block 2 is filled in the the contents of the corresponding cells will be looked up and 'Best' will display the highest value. This would then be repeated when block 3 is filled in etc.

I hope this makes sense, and I really hope somebody can help.

Many thanks

Gmunroexample doc.xlsx

[Melvinrobb]

IF A3 was "6A", and H3 was "6B", what would you want displayed in V3?

[FDibbins]

Try this. In AD3 down, copy this to the end(AD23)
=COUNTA($AC$3:$AC$93)-ROW(A1)+1 - this will start counting at the highest number downwards

then use this in V3, copied across...
=INDEX($AC$3:$AC$23,MATCH(MAX(VLOOKUP(A3,$AC$3:$AD$23,2,FALSE),VLOOKUP(H3,$AC$3:$AD$23,2,FALSE),VLOOKUP(O3,$AC$3:$AD$23,2,FALSE)),$AD$3:$AD$23,0)*1)

If the order is not the way you want it, look at changing the sequence of the data in column AC

[Gmunro]

Hi Melvinrobb

I would want 6A displayed as it is the greater mark.

Many thanks

[Gmunro]

Hi FDibbins

Many many thanks, this works in respect of taking the highest grade, but it requires all of block 1, 2 & 3 to be filled in. I am looking for the formula to display the best grade if only block one is filled in, then block 1&2, then block 1&2&3. This will allow me to always look at the best section throughout he year and see the students best grade for each section, 'Explore/Plan/Make etc etc. I have made this example with three blocks but once I have the formula it will be replicated in a very large spreadsheet containing 100's of blocks. So the spreadsheet must work as and when data is added but ignore any blank cells.

I hope this is understandable

Many thanks again for your time and support

[FDibbins]

Ok I had a feeling that would be the case. Lets try a different approach. It would be easier if we were working with numbers, so lets convert the "score" to the values we calc'd in AD.

In a helper row (I used 4), copy this across...
=VLOOKUP(A3,$AC$3:$AD$23,2,FALSE)
then in V3, use this array formula...
=INDEX($AC$3:$AC$23,MATCH(MAX(IF(A2:T2=MID(V2,SEARCH(" ",V2,1)+1,99),A4:T4,0)),$AD$3:$AD$23,0),1)
If you can remove the "Best " from your titles, then you can shorten it to...
=INDEX($AC$3:$AC$23,MATCH(MAX(IF(A2:T2=V2,A4:T4,0)),$AD$3:$AD$23,0),1)

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Once you have the answer, you can copy it across

[Gmunro]

Hi FDibbins

Again many thanks for your help. This works exactly how the first formula worked, it is very accurate and does what I desire but only of the data is in all cells.
I need the formula to work if only block one is complete i.e. (1 Explore grade, 1 Plan Grade, 1 Make Grade etc).
The current formulae will only display a grade if all cells are complete i.e., A3,H3 & O3.

If A3 has a grade in it I wish V3 to display it because its the only grade. (even if H3 & O3 are blank)
As and when H3 has a grade filled in I want the formula to select the highest and display it in V3 (again even if O3 is blank)
As and when A3, H3 & O3 has a grade in it I wish the formula to lookup and display the highest in V3
Ans this repeated for all different areas, Explore, Plan, Make etc.

I think this is a little clearer.