Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    IF & MATCH (2003)

    Hi all,
    iam trying to match a&b with m&n columns data,if match then compare f&I data with column O data for M&n and if it >= then flag it with "yes".sample of the data is attached.
    thanks in advance.
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF & MATCH (2003)

    Not sure I understand completely by is this what you are after in P3 (copy P3 to P4:P17)

    It is an array formula (adjust the ranges as needed), confirm with ctrl-shift-enter
    =IF(SUM(IF(($A$3:$A$17=M3)*($B$3:$B$17=N3),($F$3:$ F$17+$I$3:$I$17)))=O3,"Yes","No")

    Whenever both the value in M3 matches the a value in Col A and the value in N3 matches a value in column B, it sums the values of column F and column I. If that sum equals the value in O3, it gives a "Yes" otherwise it gives "no"

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF & MATCH (2003)

    A non-array formula that could be used in P3 and copied is:
    =IF(SUMPRODUCT(($A$3:$A$17=M3)*($B$3:$B$17=N3),($F $3:$F$17+$I$3:$I$17))=O3,"Yes", "No")

    If you do not need the Yes/No but would accept a True/False instead it could be shortened to:
    =SUMPRODUCT(($A$3:$A$17=M3)*($B$3:$B$17=N3),($F$3: $F$17+$I$3:$I$17))=O3

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF & MATCH (2003)

    Steve,
    Not exactly, I have included one more name in a and b columns to explain, I also included your formula results and what I need. If you look at q3(expected results) the formula will check the names in a3 and b3 with the list in m3:m17 and n3:n17, after match,get the corresponding value from o317 which is 14 and then compare it with f & the I values for c3:c10 and then do the next one a11&b11.
    TIA
    dubdub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & MATCH (2003)

    I'm sorry, I still don't understand. The "expected results" column makes no sense to me. Could you try to explain in detail how you arrive at "YES" in K3 and K4, and "NO" in K5, K6 etc.?

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF & MATCH (2003)

    HI HANSV,

    FOR P_CD=XXX(COL A) AND RSVR_CD=MMMM(COL [img]/forums/images/smilies/cool.gif[/img] FOR W_NUM=1(COL C) THE WC VALUES (COLS F&I) EQUALS 33 & 32. THESE TWO VALUES ARE GREATER THAN(>) THE REFERENCE VALUE FOR XXX & MMMM VALUE IN COLS M5&N5 WHICH IS 14, THEREFORE THE RESULTS ARE YES AND IN K5&K6 FOR W_NUM 5&8 (COL C) THE RESULTS IS NO BECAUSE THE WC VALUES ARE LESS THAN(<)14. THE EXPECTED RESULTS ARE MANUAL.

    DUBDUB
    TIA
    dubdub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & MATCH (2003)

    Watch that Caps Lock key - using ALL CAPS is the internet equivalent of shouting.

    In the original post, you mentioned >= (greater than or equal to), now you mention > (greater than). Which is it to be?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & MATCH (2003)

    Does the attached workbook do what you want? If you want > instead of >=, change the formulas accordingly.
    In row 5, the formula returns Yes because 5.40 + 9.07 is larger than 13.57. If you want the formulas to look at rounded values, use the ROUND function, e.g.

    =IF(ROUND(F3+I3,0)>ROUND(SUMPRODUCT(($L$3:$L$17=O3 )*($M$3:$M$17=P3)*($N$3:$N$17)),0),"Yes","No")

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF & MATCH (2003)

    Perfect,
    Sorry for the Caps lock, it was on from my previous work prior sending the post and it is a very hectic day.
    Thanks.
    TIA
    dubdub

Posting Permissions

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