Ranked Index Match Help

[VarBod]

Attachment 648610

Hi all.

Ive been struggling with this formula for a while now.


The Table shows the profit and loss for certain Days on different traded symbols. Im looking for a formula that shows what symbol lost the most money on a certain day.
For example on 19/06/2019 what symbol lost the month money, and then a formula for what symbol lost the second most money.

Would greatly appreciate some help. Its for a Uni project due next week and ive had no luck with youtube or google so far.

Many thanks

[KOKOSEK]

Could you attached a file, please?
Check yellow banner at top of forum.
Help us to help You.

[VarBod]

Thanks you for the helpful advice.

I have attached the example. The data is already made up as i didnt want to show sensitive data.

Much appreciated.

[Glenn Kennedy]

I don't want to cheat YOU out of YOUR education. What have YOU tried so far? How do YOU think that this needs to be tackled?

[VarBod]

I appreciate the chance to learn.

I wasnt even sure where to start so ive broken the question down into parts so i can better learn.

I have updated the spreadsheet and highlighted my workings in a yellow box

1) Lets assume i know what the smallest pnl on the 20/06/2019 was by using a Small() Function.

2) Now i want to know what symbol that pnl belongs to. This is what i struggle to return. Im not sure if an index match is the right formula to use.


For example if i wanted to rerutn the pnl for WTI on the 20/06/2019 i know i can return that using an index match (blue cell) but i cant figure out how to reverse engineer getting the symbol from knowing the pnl

[KOKOSEK]

Put into B18:

Please Login or Register  to view this content.

as it is array formula accept it with Ctrl+Shift+Enter not just Enter as usual and then drag it 3 times down (for 1st, 2nd and 3rd rank).

	A	B
17	Ranked Losing Symbol	20/06/2019
18	1	WTI
19	2	Nasdaq
20	3	Amazon

	A	B
17	Ranked Losing Symbol	14/06/2019
18	1	Apple
19	2	WTI
20	3	Tesla

Should works.

[VarBod]

Thanks a lot for the help.

Greatly appreciate it. Hadnt heard of an offset formula before but am now looking at youtube tutorials for it