Thanks:  0
Likes:  0

# Thread: Multiple matches using the IF function

1. 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?

2. 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

3. 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.

Try to re-enter the formula I suggested lierally.

4. Your list is not in ascending order.

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

5. 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.

6. 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 ]

7. 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

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

9. The formula that I suggested gives the correct answer.

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

10. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•