Solved: referencing different cells for calculations

[blumfeld0]

i am a finance guy (or trying to be) and am calculating implied volatilities and the Greeks in excel using the newton-raphson method via the black-scholes equation. that is no problem but organizing the data into excel is causing me to lose a lot of sleep because i have thousands of rows of data.

here is what my current dilemma looks like in :
A B C D E F
800 .05 8 F random number result
G random numbers result
I random number result
J random number result
I am using A1,B1,C1 to calculate some stuff---these cells are fixed.
the letters in column D range from F->J and the frequency of how often these numbers occur in a row is random. so there could be 10 F's in a row and 12 G's and 2 H's, 8 K's, 7 L's, 4 X's in a row. i just put some letters as an example above.
column E contains some useful random numbers for the calculation.
column F is my result.
well as it turns out A1, B1, C1 are not fixed! and depend on the letter contained in column D! they change for each letter.
I have separate worksheet where i make all these calculations and use $A$1,$B$1,$C$1. but now i need to changethe input(A1,B1,C1) of the formulas in such a way that it depends on the letter contained in column D above.
any ideas on how to deal with this situation? maybe its something obvious i am missing.

thank you everyone!

[anandbohra]

:hi:blumfeld0

pl attach small example excel file & mention there what exactly u want coz many a times example explains better than notes.

in excel file give what u r getting right now
what output u want if certain cell changed

[blumfeld0]

Thank you. I should've posted an example the first time.
please see the attachment called template. it contains 2 worksheets explaining what I am trying to accomplish.
thank you for youe help!

[Bob Phillips]

Use VLOOKUP.

For example, to get Price,

=VLOOKUP(D2,Calculations!$A$10:$D$15,2,FALSE)

Interest rate

=VLOOKUP(D2,Calculations!$A$10:$D$15,3,FALSE)

etc.

[blumfeld0]

thanks for the reply.
ok i read about VLOOKUP. sorry but i am still very confused
are you saying that everytime a have a formula (contained in wksht "calculations" that contains $A$1,$B$1,$C$1 as input, i can simply replace that with VLOOKUP( )?

the data i have is:
Letter Price Interest Rate Time F 850 6 8 G 834 5 20 H 200 4 10 I 700 3 7 J 650 2 9
and this is what i need to reference as the input of the formulas in wksht "calculations" depending on the letter (F,G,H,I,J etc. in this case). the price, interest rate, time change as the letter changes but they are the same for the same for the same letter.

i think i need more explicit explanation. thank you.

[figment]

i might be miss inturpreting what you are trying to do, but i have appended you results page with what i think your trying to acheave, it will obvcours need to be adapted to your exact data fields but the idea should be there.

[Bob Phillips]

thanks for the reply.
ok i read about VLOOKUP. sorry but i am still very confused
are you saying that everytime a have a formula (contained in wksht "calculations" that contains $A$1,$B$1,$C$1 as input, i can simply replace that with VLOOKUP( )?

the data i have is:
Letter Price Interest Rate Time F 850 6 8 G 834 5 20 H 200 4 10 I 700 3 7 J 650 2 9
and this is what i need to reference as the input of the formulas in wksht "calculations" depending on the letter (F,G,H,I,J etc. in this case). the price, interest rate, time change as the letter changes but they are the same for the same for the same letter.

i think i need more explicit explanation. thank you.

I am saying that, as I read your worksheet, you do VLOOKUPs for A1,B1 and C1 embedded within the other fdormulae so that you can get a variable price, interest rate, etc.

[blumfeld0]

Perfect. I got it thank you!
excel is pretty cool
you guys just saved me 4-5 days work.