CODEWITHSUNDEEP
excelexcel-formula
JohnD
JohnD

Reputation: 353

Using MATCH to match up two pairs of columns

I have columnA for item name and columnB for quantity and respectively the same for COLC and COLD. I need to check if the items in COLA exists in COLC. If it does, I need that item's qty(COLB) to match the same item's QTY on COLD. For example, A2 exists in C2 then B2 needs to match D2. if match, return match, else not match.

 COLA    COLB    COLC   COLD
 Tea     1        Tea     2
 Coffee  2        Coffee  2 
 Soda    3        Water   1

So far I have this but it's not working. I think it's the (C&C:D&D) part that I need to fix. =if(iserror(match(A1&b3,(C&C:D&D),0)),"NO MATCH", "MATCH")

Any ideas?

Upvotes: 0

Views: 4704

Answers (2)

Shauno_88
Shauno_88

Reputation: 665

If the items in ColA and ColC are not in the same order, or even the same number of items this should work.

=IFERROR(IF(VLOOKUP(A1,C:D,2,FALSE)=B1,"Match","No Match"),"")

It uses a VLOOKUP, so their can't be duplicates of the same item in ColC as VLOOKUP's will select the first cell it matches too.

Upvotes: 1

Dane I
Dane I

Reputation: 742

This I think will work:

=IF(AND(A2=C2,B2=D2),"MATCH","NOT MATCH")

Upvotes: 0

Related Questions