Multiple matches using the IF function
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Multiple matches using the IF function

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

    Default

     
    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!

  2. #2
    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 11:18, JRRT wrote:
    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!
    Don't understand at all.

    Care to try again using short sentences?

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

    Default

    could it be

    =VLOOKUP(E2,$A$24:$D$28,4,0)*D2

    ??

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

    Default

    The formula will be in F2 what I would like the formula to do is look at the text that is typed in E2 and match it up with what text is put in cells A24 through A28.
    If it finds a match (i.e. A25)it needs to take the Number that is put the same row in cell D24 through D28 (i.e. D25). Then it needs to multiply that number it matched (i.e. the number in D25) by the number that is put in cell d2.
    I hope that is better, if not I can e-mail the spreadsheet that would illustrate it better

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jrrt,

    Hi JRRT,

    How about

    =INDEX($1:$65536,MATCH(E2,YourRange,0)+ROW(YourRange)-1,COLUMN(YourRange))*D2

    Specifically for you,
    =INDEX($1:$65536,MATCH(E2,A24:A28,0)+ROW(A24:A28)-1,4)*D2

    Note that there is no error checking here.

    HTH,
    Jay

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

    Default

    The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-21 12:03, JRRT wrote:
    The formula =VLOOKUP(E2,$A$24:$D$28,4,0)*D2 seems to work. What is the "4,0" towards the end used for?
    Return the 4th column of the lookup table, $A$24:$D$28. 0 means perform an exact match. See the Excel Help topic for "VLOOKUP worksheet function".

    [ This Message was edited by: Mark W. on 2002-03-21 12:11 ]

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

    Default

    I made a mistake the formula doesn't do the math correctly. I think it finds the right cells but it multiplies them incorrecly. For example it is supposed to multiply D2 which has the number 30 by the number in cell D23 which has the number 79. It gives me the total of 2,401.60 but the correct answer is supposed to be 2,370. I looked at the help in excel but can't figure it out. This is the formula I am using which is located in cell F2: =VLOOKUP(E2,$A$23:$D$28,4,0)*D2
    Thanks

  9. #9
    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 13:36, JRRT wrote:
    I made a mistake the formula doesn't do the math correctly. I think it finds the right cells but it multiplies them incorrecly. For example it is supposed to multiply D2 which has the number 30 by the number in cell D23 which has the number 79. It gives me the total of 2,401.60 but the correct answer is supposed to be 2,370. I looked at the help in excel but can't figure it out. This is the formula I am using which is located in cell F2: =VLOOKUP(E2,$A$23:$D$28,4,0)*D2
    Thanks
    Would it be:

    =INDEX(D24:D28,MATCH(E2,A24:A28,0))*D2


    or better:

    =IF(COUNTIF(A24:A28,E2),INDEX(D24:D28,MATCH(E2,A24:A28,0))*D2,0)

    Aladin

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

    Default

      
    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?

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