List checking with Match() and Countif()

Check data completeness using MATCH and COUNTIF (intermediate)

• After downloading detailed information from a system there is often a need to check if your spreadsheet is up to date e.g. if you are reporting sales product and new products are constantly coming on line. Using COUNTIF or MATCH are two good ways to check if there are new products to be included in your spreadsheet.
• COUNTIF method:
  • in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =COUNTIF(array, item) where array equals existing list of products in your spreadsheet and Item references the product name in the download.
  • This will return 1 if the product exists or zero if it doesn’t. A zero means that there is a new item in the download that doesn’t exist in your data.
  • Use conditional formatting to highlight zeros –especially if the list is long.
• MATCH method:
  • in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =Match(item, array, 0) where item references the product name in the download, array equals existing list of products in your spreadsheet and the zero will look for exact matches only.
  • This will return a number representing the row the product is on within your existing data or #N/A which means that there is a new item in the download that doesn’t exist in your data.
  • NB for the array part of the formula you could select the whole column that contains existing products. The MATCH formula will then return the row in the spreadsheet where the product is contained making it easy to find should you need to do this