Return Latest or Earliest

[robocop1906]

I’d like to do a lookup on column A and return the highest number so per the example below (lookup of STT would return “2”)

Then I’d like a separate formula that used the same criteria but instead of returning what’s in column B, I’d like to return what’s in column C so per the below example it would be (lookup STT and return Lion)


Column A

STT
STT
TT
TP
TP

Column B
1
2
-3
-3
5

Column C
Tiger
Lion
Bear
Cat
Dog

 

[jim may]

Maybe...(???)

Excel Workbook
	A	B	C	D	E	F
1	STT	1	Tiger			Lion
2	STT	2	Lion
3	TT	-3	Bear
4	TP	-3	Cat
5	TP	5	Dog
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F1	=INDEX(C1:C5,MAX(COUNTIF(A1:A5,A1:A5)))

 

[robocop1906]

That worked for the example given but if you resort the data and say have TP in the middle of STT it does not work

 

[jim may]

Then maybe...

Excel Workbook
	A	B	C	D	E	F
1	STT	1	Tiger			Lion
2	STT	2	Lion
3	TT	-3	Bear
4	TP	-3	Cat
5	TP	5	Dog
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F1	=INDEX($C$1:$C$6,COUNTIF($A$1:$A$5,INDEX($A$1:$A$5,MODE(MATCH($A$1:$A$5,$A$1:$A$5,0)))))