Calculating a value and posting total to 1 of 4 fields dependant on the content of another

[CRAIGSUV]

Hi, bit of a newbie to Excel ( 2010 ) I can find my way around a bit and generally seek answers to problems using Google but I wouldnt know how to word this so would appreciate any help you knowledgeable people could provide.

I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.

proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).

F2 = 150
G2 = Q2
H2 = 10%

I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. Any ideas please. I have attached an example which hopefully makes sense of what I have wrote. Thank you.

[ragulduy]

put:
=IF($G2=LEFT(I1,2),$F2*$H2,"")
in I2 and copy across to L2.

[CRAIGSUV]

Thank you yudlugar that worked a treat, no idea how the formula works but that has saved me a lot of time. Cheers

[ragulduy]

Hi, it is worth using the formula evaluating tool to step through a formula so you understand it but to briefly describe:
LEFT(A,n)

takes the first n letters of the text A.

So Left(I1,2) returns the first 2 letters of I1, which is Q1.

=IF(a=b,c,d)
states that if a is equal to b, return c, otherwise return d.

So:
=IF($G2=LEFT(I1,2),$F2*$H2,"")
states that if G2 equals the first 2 letters of I2, then return F2 * H2, else return "".

then when it is copied across the I1 become J1 and so on.

[CRAIGSUV]

ah I see, fairly straight forward when explained so clearly. I really appreciate your help. Thank you.