|
MISSMATCHES and MATCHES Examples
(This example uses modified sample data from”M is for (DATA) Monkey” by Ken Puls and Miguel Escobar, ISBN 978-1-61547-034-1)
The Inventory Items table
.png)
The sales transactions table
.png)
There are potential problems with this data: the sales table contains SKUs that do not exist in the inventory table, and the inventory table has SKUs that do not exist in the Sales table. We can use MISSMATCHES to find the miss-matches between the two tables.
=MISSMATCHES(K7:N27,A7:H20,,,"**") using the default match type of 3 and a No_Match of “**” produces
.png)
Using match type 1 only shows the miss-matches in the left transactions table:
.png)
And match type 2 only shows the miss-matches from the right inventory table:
.png)
To see both the matches and missmatches for the left transaction table use this formula;
=MATCHES(Sales[#All],Inventory[#All])
But the SKU Number and Brand appear twice in the ouput (once from the left table and once from the right table). And often you need to specify which columns to use for the match, rather than using the default of matching on column headers.
Use the function wizard to see expanded details for the arguments and to call up help for the function.
.png)
=MATCHES(Sales[#All],Inventory[#All],,"SKU Number",,{"Brand","Sku number"})
.png)
|
