Last year, I did a TikTok video blog post about Excel’s =ROMAN function (watch it here). It will convert any regular Arabic number to Roman. For example, =ROMAN(2026) will return MMXXVI. I noted that there is no built-in function to go the reverse direction. Thanks to Reddit user @real_barry_houdini, here is a formula that will do it (as long as you’re running Microsoft 365).
If the Roman number is in A1, use this:=XMATCH(A1,ROMAN(SEQUENCE(5000),0))
How does it work?
The Sequence function generates a list of numbers. It has several options, but if you leave them off, =SEQUENCE(5000) returns a list of numbers from 1 to 5000. Since this function is nested, it won’t actually insert the numbers on the sheet, and Excel keeps the list in memory.
The Xmatch function is using three arguments: what are we looking for, where are we looking, and return an exact match.
So this formula is telling Excel: look at the Roman in A1. Now generate an army of Romans with values from 1 to 5000. When you find the Roman that matches what’s in A1, return it. So if A1 contains MMXXVI, =XMATCH(A1,ROMAN(SEQUENCE(50000),0)) will return 2026.
If the Roman you want to convert is greater than 5000, Excel will throw a #N/A error, or you can stuff this whole formula inside the =IFERROR function. But you can increase the upper limit to whatever you need.
Also: the Between the Sheets video blog will return.