INDEX/MATCH for LARGE formulas all within the same cell

You had the answer. It was just a matter of combining the formulas. There is a technique for that.

First I made a formula using the helper cells: =LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)&" from " & F9 & " " &G9 & " on " &TEXT( I9,"mm/dd/yyyy")& " in " & K9

Then I substituted (copied and pasted) the formula in Cell K9 for K9 in this formula and so on for the other helper cells. Along the line some of these formulas contained D9 so I had to do a second round of pasting. In general, keep copying and pasting until you have every sub-formula in terms of the basic elements, in this case cell A1.

This process yields the hairy and completely incomprehensible formula: =LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)&" from " & INDEX(MasterData[Field],MATCH(IF(MasterData[Name]=$A$1, LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)),MasterData[Value],0)) & " " &INDEX(MasterData[Product],MATCH(IF(MasterData[Name]=$A$1, LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)),MasterData[Value],0)) & " on " &TEXT(INDEX(MasterData[Date],MATCH(IF(MasterData[Name]=$A$1, LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)),MasterData[Value],0)),"mm/dd/yyyy")& " in " & INDEX(MasterData[Region],MATCH(IF(MasterData[Name]=$A$1, LARGE(IF(MasterData[Name]=$A$1, MasterData[Value]),$A5)),MasterData[Value],0))

Which is why I like to use helper cells when I can. In this case finding the row on which the LARGE is found would knock out many of the subsequent sub-formulas where large is used.

Describe what you mean by duplicate and how you want to handle the duplicate. What tie breaker criteria do you want to use.

The answer is probably to wrap an if statement around the formula from Hades.

OK, this means we need to do a check to see how many times the largest value occurs and then look for the first occurrence, then if needed, the second occurrence , then if needed the third occurrence and then second largest and check for the tie there, then third if not already taken care of. This logic limits to a top 3. Unless we add something artificial to the data such as row()/10000000 and display a truncated value. Offhand, I can't see doing this without a helper cell on the data side at least.

I reworked the "helper cells" on row 9. Now that we have ValueHelper, we can do a match against it. I substituted the pieces back and got:
=TEXT(LARGE(IF(MasterData[Name]=$A$1, MasterData[ValueHelper]),$A5),"#") & " from " & INDEX(MasterData,MATCH(LARGE(IF(MasterData[Name]=$A$1, MasterData[ValueHelper]),$A5),IF($A$1=MasterData[Name],MasterData[ValueHelper],FALSE),0),3) & " " & INDEX(MasterData,MATCH(LARGE(IF(MasterData[Name]=$A$1, MasterData[ValueHelper]),$A5),IF($A$1=MasterData[Name],MasterData[ValueHelper],FALSE),0),4) & " on " & TEXT(INDEX(MasterData,MATCH(LARGE(IF(MasterData[Name]=$A$1, MasterData[ValueHelper]),$A5),IF($A$1=MasterData[Name],MasterData[ValueHelper],FALSE),0),4),"mm/dd/yyyy") & " in " & INDEX(MasterData,MATCH(LARGE(IF(MasterData[Name]=$A$1, MasterData[ValueHelper]),$A5),IF($A$1=MasterData[Name],MasterData[ValueHelper],FALSE),0),2)

Which is about as ugly a formula as I've seen in Excel .

There are a couple of things that I am compulsive about. One is that the Match must have the ,0 (I think that's the default anyway.) The other is =if (Value=Range, NewRange, False) - again, if a secondary range is not defined, the if statement will default to False. I have some OCD when it comes to being explicit in my coding.