Calculate Max of several cells and print the associated text

[BrerRabbit]

Please ignore my previous post.

How do I calculate the max number below and have the answer come back as the three letter code? How do I also just pick teh first instance of there's only one of each?

How do I also delete my previous post?

1 - APP		2 - ORA	4 - BAN	1 - CUC
1 - APP			1 - BAN

 

[RobP]

Hi,
unfortunately I don't believe you can solve your '3 letter return' in Excel 2016, due to its lack of array options.

Even with latest Excel365 its a pretty complex formula (but its do-able). I've shared that below (result formula in C5, other formulas as helpers in A5 & B5) for a couple reasons -
a) maybe you upgrade Excel at some point, b) maybe someone else here can find a way in legacy excel by seeing how I did in Excel365).
Sorry its not helpful for you in Excel 2016 directly.

It effectively takes your array of cells, and puts them in a column (removing blanks - as you can see in A5). then scans each and extracts the numbers on the left, then finds the MAX of these.
It then uses this MAX value to search the list again, and returns the value of this cell relating to the MAX. Lastly, it transforms that into the last 3 digits to display.

In terms of your second question, what's the context of your selection of the first instance ? (are you scanning across the columns, down the columns, do they appear in multiple columns etc.). Why do you want/need to select it in the first place ? There is maybe not enough info here for us to advise on that yet.

Book1
	A	B	C	D	E
1	1 - APP		2 - ORA	4 - BAN	1 - CUC
2	1 - APP			1 - BAN
3
4
5	1 - APP	4	BAN
6	2 - ORA
7	4 - BAN
8	1 - CUC
9	1 - APP
10	1 - BAN
Sheet1

Cell Formulas
Range	Formula
A5:A10	A5	=TOCOL(A1:E2,1)
B5	B5	=TEXT(MAX(MAP(TOCOL(A1:E2,1),LAMBDA(v,VALUE(LEFT(v,FIND(" ",v)-1))))),0)
C5	C5	=RIGHT(XLOOKUP(TEXT(MAX(MAP(TOCOL(A1:E2,1),LAMBDA(v,VALUE(LEFT(v,FIND(" ",v)-1))))),0),TOCOL(A1:E2,0),TOCOL(A1:E2,0),,1),3)
Dynamic array formulas.

 

[BrerRabbit]

I played around with it, and I came up with this. I'm surprised that I did. But have you ever noticed that when you ask someone else the question the answer becomes apparent?

12 APP

0​

9 BAN

11 GRA

12​

=MAX(IF(B16="","0",LEFT(B16,2)),IF(C16="","0",LEFT(C16,2)),IF(D16="","0",LEFT(D16,2)), IF(E16="","0",LEFT(E16,2)))
Then I wrapped it in Address to find which three letter code was the most and it seems to work.

I've had to do a calendar array where I need 3 bits of information. Excel likes to do one cell one value, whereas I've done 3 different values in one cell. With this I'm counting how many times the three letter code comes up in a particular row. Lets me know how many times that person has done that class. This was the last formula I needed.

Thank you for your efforts though, I appreciate it.

 

[RobP]

Great - glad you managed to solve your problem..

cheers
Rob