Multiple matches using the IF function

[JRRT]

The formula I am using will be in F2 I want to have it look at what is in cells A24 through A28 to find the match that is in E2 then multiply it by what is in d2 by what it finds on the match that is the right of the search that it did on A24 through A28 which is in the D24 through D28.
I know this is confusing if you need more of an explination let me know.
I have come up with part of what I need but can't figure how to get the last multiplication part. here is what I have so far: =($A$23:$A$28=E2)*D2*D24. Thanks for your help this site is amazing!

 

[Aladin Akyurek]

On 2002-03-21 14:33, JRRT wrote:
With both formulas it still comes up with the answer 2401.60 but the answer is supposed to be 2370. I can't seem to find why it is doing it wrong. I tried both formulas and both give the incorrect answer of 2401.60 any ideas why?

Could you post what is in A24:A28, D24:D28, D2, and E2?

Aladin

 

[JRRT]

Here it is Thanks.

A24:A28 has the following:
Pioneer 33R87
Pioneer 3237
Pioneer 3436
Pioneer 33P66
empty


D24:D28 has the following
85.00
89
80
79
0

D2 has the number 30

E2 has Pioneer 33P66

 

[Aladin Akyurek]

On 2002-03-21 15:23, JRRT wrote:
Here it is Thanks.

A24:A28 has the following:
Pioneer 33R87
Pioneer 3237
Pioneer 3436
Pioneer 33P66
empty


D24:D28 has the following
85.00
89
80
79
0

D2 has the number 30

E2 has Pioneer 33P66

I get 2370!

It seems you're retrieving the wrong number from the D-range.

Aladin

Try to re-enter the formula I suggested lierally.

 

[Dave Patton]

Your list is not in ascending order.

Use =VLOOKUP(E2,A24:D28,4,FALSE)*D2

 

[Aladin Akyurek]

On 2002-03-21 15:39, Dave Patton wrote:

Your list is not in ascending order.

Use =VLOOKUP(E2,A24:D28,4,FALSE)*D2

Dave,

I'm using MATCH(...,0) as part of the formula. So, that should be OK, and it's OK.

Aladin

 

[IML]

On 2002-03-21 15:39, Dave Patton wrote:

Your list is not in ascending order.

Use =VLOOKUP(E2,A24:D28,4,FALSE)*D2

Nor does it need be. From the help file:

"If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted."

Is it possible your D2 value is really 30.43797 and you are just showing the first two places????
This message was edited by IML on 2002-03-21 16:56

 

[JRRT]

I think I figured out the problem. In cell D2 the number that is there which in this case is 30 but the cell gets the number by using the following formula:
=IF(B2="i",C2/2.5,IF(B2="d",C2/4,0))
When I take the formula out and just type the number 30 in cell D2 the correct answer of 2,370 appears in cell F2 like its supposed to. How can I keep the formula in cell D2 and F2? Thanks

 

[dantb]

Maby =VLOOKUP(E2,A24:C28,2,FALSE)*D2 If I understand correctaly

 

[Dave Patton]

The formula that I suggested gives the correct answer.

Please read the Help that you quoted.

Other answers are certainly possible; I
did not state that the formula presented was the only answer.

 

[IML]

On 2002-03-21 16:57, JRRT wrote:
I think I figured out the problem. In cell D2 the number that is there which in this case is 30 but the cell gets the number by using the following formula:
=IF(B2="i",C2/2.5,IF(B2="d",C2/4,0))
When I take the formula out and just type the number 30 in cell D2 the correct answer of 2,370 appears in cell F2 like its supposed to. How can I keep the formula in cell D2 and F2? Thanks

you could simply round D2 (*round(d2,0))

or check precision as displayed under Tools options calculation.