Look up max alpha numeric value

Question

I have a table, one column with emails, the one next to it with serial numbers.

I want to find the maximum serial number value for every email using this formula.

=TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUAM00000")

to achieve something like this

I keep getting error #value! , after chasing my tail for so long I observed something really strange.. the formula works , as long as the serial does not have the letter "M" or apparently "N" too!!

so =TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUA**X**00000") works! also =TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUA**T**00000") works

=TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUA**M**00000") does not work =TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUA**N**00000") does not work either , but i don't care really i just found it weird that excel does not like these 2 letters.

so my question is, why is the formula working with some letters and some not , and how do i get it to work

note, i can't change the convention, its auto generated and has to be VUAM

I pretty much know it's the letter "M" that's causing the issue, i have tried using Right instead of Mid, with same result #value!

=TEXT(MAX(IF($A$1:$A$100=K3, RIGHT($B$1:$B$100, 5)+0)), "\VUAM00000")

also simplifying the formula to

=MAX(IF($A$1:$A$100=K3, RIGHT($B$1:$B$100, 5)+0))

works too ,or does its respective job in the formula, so the max part is fine , its when you add the rest the error occurs.

Answer 1

You could try using the following formulas, this assumes there is no Excel Constraints as per the tags posted:

---

=TEXT(MAX(--TEXTAFTER(B$2:B$7,"VUAM")*($A2=A$2:A$7)),"V\U\A\M\00000")

---

Or, using the following:

=TEXT(MAX((--RIGHT(B$2:B$7,5)*($A2=A$2:A$7))),"V\U\A\M\00000")

---

Or, you could use the following as well using XLOOKUP() & SORTBY():

---

=LET(
     x, SORTBY(A2:B7,--RIGHT(B2:B7,5),-1),
     y, TAKE(x,,1),
     XLOOKUP(A2:A7, y, TAKE(x,,-1)))

---

^{§ Notes On Escape Characters: The use of backslash before & after the V, U, A & M is an escape character. Because the V, U, A & M on its own serves a different purpose, we are escaping it meaning hence asking Excel to literally form text with that character.}

---

Here is the Quick Fix to your existing formula, escape characters are not placed correctly, info on the same refer §

---

=TEXT(MAX(IF($A$2:$A$100=A2, MID($B$2:$B$100, 5, 5)+0)),"V\U\A\M\00000")

---

One more alternative way using SORT() & REPT() with Implicit Intersection Operator ^@

---

=@SORT(REPT(B$2:B$7,(A2=A$2:A$7)),,-1)

---

_{Notes on Implicit Intersection Operator^@ : It helps in reducing many values to a single value. 1.) If the value is a single item, then return the item, 2.) If the value is a range, then return the value from the cell on the same row or column as the formula., 3.) If the value is an array, then pick the top-left value. --> Excerpts taken from MSFT Documentations, read here.}

---

Answer 2

=VLOOKUP(A1:A6,SORT(A1:B6,2,-1),2,0)

Or if already sorted as in example: =XLOOKUP(A1:A6,A1:A6,B1:B6,,,-1)

Answer 3

Your letters M and N give a problem in the TEXT function they are characters that need to be escaped to be used literally in that function. You can escape those characters by preceding them with a \, which escapes only the single character following that token, or by enclosing them within quotes.

so "\V\U\A\M0000" or """VUAM""0000" should work OK.  

If your serial numbers are sorted ascending as you show in your example, and they all start with the same four letters, you can use, in most versions of Excel:

=LOOKUP(2,1/(A2=$A$2:$A$7),$B$2:$B$7)

If you have 365, you could use the more understandable:

=CHOOSEROWS(SORT(FILTER($B$2:$B$7,$A$2:$A$7=A2),1,-1),1)