How to use the Vlookup & Match function in the working file attached?

[SONJOE JOSEPH]

Dear All Excel Experts,

I have an excel sheet were the sheet is linked to the NSE option chain site. This data will be refreshed every 5 mins. Once u open the file u will see the Strike price which is 8800 now. As the user changes the values in cell "Z1" say 8900 (it can be any four digit No) in cell "Y5" the formula has to be "cell X5 value + the Bid Price of strike 8900 ie 38.55. This sum value will come in cell "Y5".

Similarly in cell "Z5" the formula has to "cell Y1value + the Ask Price of strike 8900 ie 134. This sum value will come in cell "Z5".

How can i use the Vlookup and match functions effectively in the above which i have explained. Now i'm updating this cell "Y5 & Z5" manually were i need to search the Bid price and Ask Price.

Do help me out in solving this problem...Thank You guys and awaiting your feedback.

Regards,

Sonjoe Joseph.

 

[SONJOE JOSEPH]

Dear All Excel Experts,

I have an excel sheet were the sheet is linked to the NSE option chain site. This data will be refreshed every 5 mins. Once u open the file u will see the Strike price which is 8800 now. As the user changes the values in cell "Z1" say 8900 (it can be any four digit No) in cell "Y5" the formula has to be "cell X5 value + the Bid Price of strike 8900 ie 38.55. This sum value will come in cell "Y5".

Similarly in cell "Z5" the formula has to "cell Y1value + the Ask Price of strike 8900 ie 134. This sum value will come in cell "Z5".

How can i use the Vlookup and match functions effectively in the above which i have explained. Now i'm updating this cell "Y5 & Z5" manually were i need to search the Bid price and Ask Price.

Do help me out in solving this problem...Thank You guys and awaiting your feedback.

Regards,

Sonjoe Joseph.

 

[SONJOE JOSEPH]

Guys do respond looking for a solution for the above mentioned problem.

Regards,

Sonjoe Joseph

 

[bines53]

Hi,

Which Strike ,You want to find "at the money" ?

David

 

[bosco_yip]

Maybe…..

1] Y5 :

=X5+INDEX(I1:I1000,MATCH(Z1,L1:L1000,0))

2] Z5 :

=Y1+INDEX(O1:O1000,MATCH(Z1,L1:L1000,0))

Regards

 

[SONJOE JOSEPH]

Dear Bosco,

The code is working perfectly fine. Thanks a lot.

Regards,

Sonjoe Joseph

 

[SONJOE JOSEPH]

Maybe…..

1] Y5 :

=X5+INDEX(I1:I1000,MATCH(Z1,L1:L1000,0))

2] Z5 :

=Y1+INDEX(O1:O1000,MATCH(Z1,L1:L1000,0))

Regards

Dear Bosco,

I have posted another question in a new thread. Do help me out to solve the problem.

Regards,

Sonjoe Joseph.