find the highest text value in a row, and return the corresponding column header

[bumbling-idiot]

i am an architect and am trying to organise a drawing issue sheet.
I have drawing revisions listed along a row, with corresponding revision status at the top of each column
I would like a summary to appear - the first part to show the latest drawing reference issued (highest revision value), and the second part to return the corresponding column header.
i managed to find a formula online to return the highest text value in a row (i am not an excel expert so i am not sure how this works - but it seems fine)

=INDEX(D3:L3,MATCH(MAX(INDEX(COUNTIF(D3:L3,"<"&D3:L3),0)),
INDEX(COUNTIF(D3:L3,"<"&D3:L3),0),0))

I would now like (in a separate cell) to return the corresponding column header from this cell, but can't work it out.
I have attached my draft document which is probably easier to understand than my (rambling) description.

max text value and corresponding column header.xlsx

Any help would be appreciated.

Andrew

[RaulRodriguez]

Try this :
=INDEX($A$1:$H$3,1,COLUMN(INDEX(D3:L3,MATCH(MAX(INDEX(COUNTIF(D3:L3,"<"&D3:L3),0)),
INDEX(COUNTIF(D3:L3,"<"&D3:L3),0),0))))

Don't forget to hit Ctrl+Shift+Enter after pasting this formula array

[bumbling-idiot]

Raul Rodriguez.... you are a genius. Your formula does exactly what i had hoped.
With thanks from all of the 'bumbling idiots' in the office.
Andrew

[bumbling-idiot]

Just been playing a little further, and have another question....
If i copy the formula down a series of cells (some of which don't yet have data in them), the formula appears to return a random value.
I would like the formula to return a blank in this scenario; is there a way to do this?

PS. i did try one fix (using the tips section of the website) which suggested formatting cells using the custom function below:
0;-0;;@

[Marcol]

This array might work as you need

Please Login or Register  to view this content.

or, if you just need the revision letter

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter

[EDIT]
Revised formulae to allow for blanks.

[bumbling-idiot]

Marcol,
I'm not sure if it is possible to attach further excel docs to help explain.... so i'll try without.
Your formula works well for the first line, but subsequent lines return error values. I tried adding the $ to the top row reference, but it didn't help
=INDEX($D$1:$H$1,1,MATCH(CHAR(MAX(CODE(D3:H3))),D3:H3,0))
I think that it may have problems processing blank cells in the range?
I would also like it to return a blank if there is no data in the specified range.

many thanks

[Marcol]

See this workbook, I think you have missed my edit in post #5

[bumbling-idiot]

Marcol,
The attached file didn't seem to appear in post 5, but i have now been able to accessed it through your latest post..... and it works beautifully.
Many thanks for your help (and patience)
Andrew