Half way there, need to formula to display data when some cells are blank.

[Gmunro]

Hi all
I have attached a spreadsheet that is causing me difficulty. Through very good assistance on here, I currently have a formula that is displaying in V3 the highest grade when it looks up the data in A3,H3 & O3. Then this is repeated for W3 when the data is looked up in B3, I3 & P3 etc etc... BUT
I need the formula to work if only block one is complete i.e. (1 Explore grade, 1 Plan Grade, 1 Make Grade etc).(please see the example to understand what is meant by a block)

The current formulae will only display a grade if all cells are complete i.e., A3,H3 & O3.

So I am looking for the formula to:

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.

Can anyone help please

Many thanks

Gmunroexample doc with formula.xlsx

[Jonmo1]

I recommend using some helper cells.
I know it's always nice when you can get the whole caluclation done in 1 formula, but sometimes that's actually counterproductive.

I'd say do the vlookups one at a time in each block
A4 though F4, then H4 through M4, and O4 through T4
=IFERROR(VLOOKUP(A3,$AC$3:$AD$23,2,0),"")

Then your Best Grade can be a simple MAX
In V3 and filled right to AA3
=MAX(A4,H4,O4)

or
=INDEX($AC$3:$AC$23,MATCH(MAX(A4,H4,O4),$AD$3:$AD$23,0))
to get the code based on the number.

[Gmunro]

Jonmo1

Superb, thank you so much, I ca now try to add this to my big spreadsheet.

Regards

Graeme

[Jonmo1]

Great, glad to help.