How to check no match and match between two data in Excel-CodePudding

How do I compare both below sets in Excel. If Item# of Set A and Set B match then check for Date,Amount,% between the two sets. If they do not match then display "no match" else display actual(see below result). The data are unsorted.

       Set A                                       Set B


Item#  Date     Amount  %                  Item#   Date      Amount  %
4     1/3/21    $22    20%                  5     1/23/21    $220   40%
3     1/2/21    $22    20%                  4     1/2/21     $22    20%
5     1/23/21   $220   40%                  3     1/2/21     $33    20%

Result

Item#  Date     Amount  %                  
4    No Match    $22    20%
3    1/2/21    No Match 20%
5    1/23/21    $220    40%

CodePudding user response：

=CHOOSE((INDEX(F1:I4,MATCH(A9:A11,F1:F4,0),MATCH(B8:D8,F1:I1,0))=B2:D4) 1,"No Match",B2:D4)

In the above set A being range A1:D4 and set B range F1:I4 The result set is in range A8:D11

The formula first evaluates if the lookup value of set B equals A, being TRUE or FALSE. Then adding 1 converts TRUE and FALSE to 1 and 0 and adds 1. So if it doesn't match it chooses 0 1= 1. If it matches it chooses 1 1= 2.

enter image description here

CodePudding user response：

you need VLOOKUP function

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

enter image description here