Need to find the second largest No from the list?

[SONJOE JOSEPH]

Dear Excel Experts,

In Sheet 1 I have entered the "Strike Price" and "Open Interest". The CE which has the highest open interest is found in cell G5 using the Max formula. Now i need to find out the 2nd largest open interest from the Open interest column which is 3980925 ie 10800 CE. I need to get this ans in G4 and also i need to get the strike prices in the format 11000CE. I tried here vlookup but i'm gettting an error message. Simlarly in G6 i need to find the 2nd highest open interest of PE

Can somebody help me out.

Regards,

Sonjoe Joseph

Working File attached

 

[NARAYANK991]

Hi ,

The second largest , third largest , fourth largest ,... can all be found using the LARGE function. See if you can read about it here :

https://searcherp.techtarget.com/answer/Finding-the-top-three-values-using-the-Excel-LARGE-function

https://exceljet.net/excel-functions/excel-large-function

https://spreadsheeto.com/small-large/

Narayan

 

[SONJOE JOSEPH]

Thank You Narayan for your prompt response. Now i'm getting the open interest but how to get the sticke price based on the open interest R1,R2,S1 & S2. How can i use a vlookup formula here. I tried but its giving a NA. i want it in the format 10450CE hope u understood.

Do help me out...


Regards

Sonjoe Joseph

Working file attached.

 

[Hui]

H4: =INDEX($A$2:$A$39,MATCH(G4,$B$2:$B$39,0))
Copy down

VLookup is designed to Lookup the First Column and then return a data from column 2 etc
You can coerce VLookup to Lookup Left as you required in your example, but it is messy

 

[SONJOE JOSEPH]

H4: =INDEX($A$2:$A$39,MATCH(G4,$B$2:$B$39,0))
Copy down

VLookup is designed to Lookup the First Column and then return a data from column 2 etc
You can coerce VLookup to Lookup Left as you required in your example, but it is messy

Dear Hui,

Its working perfectly fine and thank you very much for your prompt response.

Regards,

Sonjoe Joseph