Help how to get cell text based on highest number

[krllb]

Hi Excel Gurus,

Need help how to get expected value (ex. Ross), where entered value = 123.
Logic is when I enter serial value = 123, it will get the name based from highest value (Repeat Serial) which is 5.
Hope you can help me out and thanks in advance.

 

[AlanY]

one way

Book1
	A	B	C	D	E
1	1	123	Name1
2	2	123	Name2	123	Name6
3	3	123	Name3
4	4	123	Name4
5	5	123	Name5
6	6	123	Name6
7	1	321	Name7
8	2	321	Name8
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(C1:C8,MATCH(MAXIFS(A1:A8,B1:B8,D2)&"-"&D2,A1:A8&"-"&B1:B8,0))

 

[nemmi69]

Is this what you wanted?
=INDEX(C2:C9,MATCH(TRUE,(B2:B9=E2),(MAX(A2:A9))))

 

[SunnyAlv]

Try This

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Arts]

Is this what you wanted?
=INDEX(C2:C9,MATCH(TRUE,(B2:B9=E2),(MAX(A2:A9))))

Hi

Could you please explain within the MATCH function how the first criteria (TRUE) works please. (Was expecting to see a reference to a cell here)

Thanks

 

[nemmi69]

So what the TRUE is doing in MATCH is that MATCH will now step through (B2:B9=E2) and (MAX(A2:A9) and when both are TRUE it returns the row (5) . This value is fed in to INDEX so it moves that number along in the range its given C2:C9, so C6 and return that cells value.

 

[Arts]

Thank you for explaining, I had no idea you could put another function in the match_type part of the formula. I thought only TRUE or FLASE could be entered here...

 

[nemmi69]

Not sure how many levels of functions in functions you can have. Not sure I have hit a limit yet.

 

[Arts]

So I could put a vlookup in the match type? (not saying I would need to but just to highlight as a point)

I was looking at your formula yesterday and just a minor point, noted that if a number was put in that wasn't in the list it would still return a value. (not a criticism of your formula in anyway)

The OP hasn't come back to any of the replies but just letting you know