Multiple matches using the IF function - Page 2
Multiple matches using the IF function
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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,327
    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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,327
    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.

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
  •  

 

 
DMCA.com