excel formula for getting underlying price of a stock

Hi friends,
I have been devising an excel to calculate the implied Volatility of the option chain based on black scholes model and its history. Nearly done with it some excel formula help is required.I have a excel sheet as under



In column C i want to get the price of the stock. For example A1=Andhrabank, B1=1/11/2016 , SO FOR getting C1, it should search in column D and row 1 for A1 and B1 and give output as 929( index and match function will do). I am stuck up as new data will be added in column A & B and new columns beyond column M will be added each day.I want to write a formula which auto calulates number of column extended beyond column M and given the output in column C.Plz help

Because you have the data table in the same sheet next to your summary, it becomes a bit more complex.

You could add a Named Range.

Go to Formulas, Define Name (in the Defined Names section)

enter a name for the range, e.g. DataTable

Then enter formula in the Refers to field: =OFFSET(Sheet1!$D$1,,,COUNTA(Sheet1!$D:$D)+1,COUNTA(Sheet1!$D$1:$XFD$1)+1)


Click Ok to accept.

Now in C1:

=INDEX(DataTable,MATCH(A1,INDEX(DataTable,0,1),0),MATCH(B1,INDEX(DataTable,1,0)))

copied down

Thanks Sir. You are a Genius. Kudos to you. Warm Regards