Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Multiple matches using the IF function

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  2. #12
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,428
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Your list is not in ascending order.

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


  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  6. #16
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #17
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #19
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,428
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  10. #20
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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