Hi all

I wonder if there's even an elegant way to do the following:

My spreadsheet is updating live from my broker's server, so I have two columns: column A: Strike prices, column B:option's price
for instance:
____________A_____________B
1 May18th 18.5 Call__________$0.38
2 May18th 19 Call____________$0.30
3 May18th 19.5 Call__________$0.28
and so on..

I have an additional cell in which I define a "budget" for the purchase, let's assume it's C3

My desired formula need to strive to buy the LOWEST strike price (column A) possible, but know that is can't pay more than the value in C3. so all I need is that the formula populate the cell it's written in with the strike price chosen, so in the example above and assuming C3(budget)= 0.35 the formula should return "19" (in the spreadsheet column A is only the strike (185/19/19.5 etc) without the date and option type, so the formula really just need to copy A2 as is).


My next step is to have a second formula in the neighboring cell to be populated with the price (column B) of the strike chosen earlier, so that in the end I'll have a log of what strike excel "chose" to buy and what he paid for it.



ANY IDEAS? thanks!