combine mid and max

[keith6292]

example.xlsxColumn A (ticket numbers)

DAS-011 E
DAS-012 E
DAS-013 E

I want to look up the maxium ticket number in column A. so all i need is the formula to return the value "013". Can this be done?

I tried creating column b using the MID formula =MID(A3,7,3) and then just doing a MAX formula but all it returns is a zero.

anyone help me please?

[AlKey]

Try this array formula in B1

=INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B
1	DAS-011 E	DAS-013 E
2	DAS-012 E
3	DAS-013 E

[Richard Buttrey]

One way as an array formula

Please Login or Register  to view this content.

However a smarter option might be to use Data TextToColumns to split the number into its own column and then just use a standard MAX(A1:A3)

[keith6292]

ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

[keith6292]

Try this array formula in B1

=INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B
1	DAS-011 E	DAS-013 E
2	DAS-012 E
3	DAS-013 E

ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

[AlKey]

="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[keith6292]

="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

that works perfect as well. now if i could make it more complicated I have something else i would like to add.

Column A

DAS-011 E
REQ-019 E
DAS-013 E
DAS-012 E
REG-020 E

Now i want to lookup just the DAS in column A and have it return the next consecutive number. so the result would be DAS-014 E

[canapone]

Hi,

same formula


="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

Hope it helps

[keith6292]

Hi,

same formula


="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

Hope it helps

I am terrible at explaining things. can you please look at my example and let me know if this is possible.

Thanks


example.xlsx

[canapone]

Hi,

confirmed with control+shift+enter


=VLOOKUP(I7,A6:B9,2,0)&"-"&TEXT(MAX(VALUE(RIGHT(D6:D17,3)+1)),"000")

could do the trick.

Regards