Excel help - find all rows where a column value doesn't match another

September 25, 2019 1:17 AM

I've got two lists as sheets in Excel. Sheet 1 is all the organisations, sheet 2 is a subset of sheet 1 with all the organisations that have been processed. I'm trying to find all the unprocessed organisations.

I can't figure out how I might use the INDEX and MATCH function in Excel to find all the rows where a value in sheet 1 column e doesn't have a organisation name match in sheet 2 column c. That is, if there's a match, don't show me that row. I just want all the unmatched rows.

posted by iamkimiam to Computers & Internet (4 answers total)

Honestly, I'd use VLOOKUP and then an IFERROR to display something human-readable like "Not processed" instead of an error message.
posted by I claim sanctuary at 1:47 AM on September 25, 2019 [1 favorite]

Best answer: In row 1 of a blank column in Sheet1, type =IFERROR(IF(MATCH(E1, Sheet2!C:C, 0)>0, ""), 1) and then copy that all the way down the blank column for as many rows as you have entries in sheet 1 column E. This will put a 1 into the unmatched rows, and leave blank the matched rows. You can replace the 1 at the end with any other message.
posted by muhonnin at 1:47 AM on September 25, 2019 [3 favorites]

Best answer: Use the equation above (or similar), but put all the data in a table first, and then you can filter the data with the arrows that show in the header row, to only show rows without matches.
posted by chrispy108 at 2:25 AM on September 25, 2019

Response by poster: Worked perfectly, thanks!
posted by iamkimiam at 3:12 AM on September 25, 2019