This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
In the inserted image the Yellow cells has this formula to find the MAX value, leaving the cell blank if value is 0 =IF(MAX(D5:ZZ5)=0, "", MAX(D5:ZZ5))
This is great for numeric values, but I have alphabetical values in the row.
In the Orange cell I tried this formula to try recognise the alphabetical characters
=INDEX(D6:ZZ6,MATCH(MAX(IF(ISNUMBER(D6:ZZ6),D6:ZZ6)),IF(ISNUMBER(D6:ZZ6),D6:ZZ6),0)) but this did not work.
How can I get the 'Current Revision' to recognise the alphabetical characters as numbers, but still return an alphabetical character in the 'Current Revision' column?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
This formula will get the verison number in max column which has value in it.
=INDEX(D6:ZZ6,MAX(COLUMN(D6:ZZ6)*(D6:ZZ6<>""))-3)
Use this one will show #Value! as Blank.
=iferror(IF(D6="","",LET(a,VSTACK(TEXT(SEQUENCE(100),"00"),CHAR(SEQUENCE(26,1,65,1))),INDEX(a,MAX(IFERROR(MATCH(D6:ZZ6,a,0),0))))),"")
Yes, you can assign a any text to a number value in another sheet then, you can use max formula on it.
=Iferror(INDEX(Sheet1!A:A,MATCH(MAX(IFERROR(INDEX(Sheet1!B:B,MATCH(D6:ZZ6,Sheet1!A:A,0)),0)),Sheet1!B:B,0)),"")
this is the error message when I try to enter this formula in.
Would it be possible to assign a numerical value to the Alphabetical characters under the 'Revision or Issue Number' column, but they still appear as letters? Then apply a MAX value formula that returned both letters and numbers and a blank cell if no data in that row? Below is the actual data I am attempting to apply this to. Would I have to have a separate tab in the Workbook that lists the alphabetical characters and their numeric value?
Try this one. It will work till version Z.
=IF(D6="","",LET(a,VSTACK(TEXT(SEQUENCE(100),"00"),CHAR(SEQUENCE(26,1,65,1))),INDEX(a,MAX(IFERROR(MATCH(D6:ZZ6,a,0),0)))))
Thank you. The above worked great.
Just a few queries based on results shown in below image.