Results 1 to 9 of 9
Thread: IF & MATCH (2003)

20060529, 06:46 #1
 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

20060529, 10:26 #2
 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 ctrlshiftenter
=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

20060529, 10:31 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: IF & MATCH (2003)
A nonarray 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

20060530, 04:42 #4
 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

20060530, 08:49 #5
 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.?

20060531, 04:52 #6
 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.
DUBDUBTIA
dubdub

20060531, 06:26 #7
 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?

20060531, 06:36 #8
 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")

20060531, 06:52 #9
 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