# Thread: IF & MATCH (2003)

1. ## 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.

2. ## 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. ## 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. ## 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.

5. ## 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. ## 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

7. ## 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. ## 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. ## 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.

#### Posting Permissions

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