Function Problems

How to Find the Maximum Revision Value with Text and Numbers in Excel

Steve K Oct 9, 2026 869 views

Question details

The user needs a way to evaluate a row of cells containing both alphabetical and numeric revision labels to find and return the highest revision value.

How to Find the Maximum Revision Value for Text and Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking document or project revisions where labels might be numbers (1, 2, 3) or letters (A, B, C), and automatically determining the most recent revision.
Observed behavior: Excel's standard MAX function only works with numbers and ignores text, making it impossible to directly evaluate and compare numeric and alphabetical revision sequences.

Before you start

Before applying these formulas, ensure you have a designated area in your workbook (such as a separate sheet) to build a mapping table that assigns strict numeric values to your alphabetical revision codes.

Solution 1Recommended

Use a Lookup Table and Array Formula to Find the Maximum Revision

Create a mapping table to assign numeric values to text revisions, allowing the spreadsheet to calculate the true maximum regardless of the cell format.

Because Excel's MAX function ignores text, comparing '1' and 'A' requires assigning a numerical rank to every possible text label. By combining INDEX, MATCH, and MAX, you can evaluate the mapped numeric ranking while displaying the original label.

Create a mapping table

On a new sheet (e.g., 'Sheet1'), list all possible revision values (1, 2, A, B, C) in Column A. In Column B, assign a corresponding numeric ranking (1, 2, 3, 4, 5) to establish the sequence.

Select the result cell

Click the cell where you want the maximum revision value to appear for your data row.

Enter the array formula

Input the formula: =IFERROR(INDEX(Sheet1!A:A,MATCH(MAX(IFERROR(INDEX(Sheet1!B:B,MATCH(D6:ZZ6,Sheet1!A:A,0)),0)),Sheet1!B:B,0)),"")

Calculate the result

Press Ctrl+Shift+Enter to apply the array formula. The formula will evaluate the highest mapped numeric value in your range (D6:ZZ6) and return the original revision label from Column A of your mapping table.

Use a Lookup Table and Array Formula to Find the Maximum Revision

Handling Empty Cells: The IFERROR wrapper guarantees that if the row contains no revision data at all, the formula gracefully returns a blank cell rather than an ugly #N/A error.

Solution 2

Find the Last Non-Blank Cell in the Row

If your revisions are strictly entered sequentially from left to right without skipping cells, you can simply extract the value from the last filled cell instead of ranking them.

Easily Manage Advanced Data Arrays with WPS Office

WPS Spreadsheet provides seamless support for complex array formulas, INDEX/MATCH combinations, and data mapping. It is an excellent tool for managing complex revision logs efficiently.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the revision data.
2. Create the mapping data: Set up a mapping sheet to define your alphanumeric ranking values in two columns.
3. Apply the formula: Paste the provided INDEX/MATCH array formula into your target cell where the max revision should appear.
4. Execute: Press Enter (or Ctrl+Shift+Enter) to instantly retrieve the correct maximum revision label.

Fully supports standard Excel formulas like INDEX, MATCH, and MAXExcellent dynamic array formula executionLightweight, fast, and completely free to use100% compatibility with Microsoft Excel .xlsx and .xls formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard MAX function ignore my letter revisions?

The standard MAX function in spreadsheet software is designed to evaluate strictly numerical values. It ignores text strings entirely, meaning a cell containing 'A' or 'Rev B' will be bypassed, which is why a lookup table is required for accurate ranking.

Do I always need to press Ctrl+Shift+Enter for these formulas?

If you are using older versions of spreadsheet software, you must press Ctrl+Shift+Enter to signal that it is an array formula. In modern versions equipped with dynamic array support, simply pressing Enter is usually sufficient.

How can I prevent the formula from showing an error when the row is empty?

Wrap your core formula in the IFERROR function, structured as =IFERROR(your_formula, ""). This tells the spreadsheet to display a blank cell instead of standard error codes like #N/A or #VALUE! when no data is present to evaluate.

Excel & Data Problems