I have two lists:

  • one "master" list containing ALL usernames and employee numbers
  • another list of say 100 random employee numbers.

I would like to filter that list based on the "random employee numbers". What would be the simplest way to perform this?

  • excel and mac. 2011? 2016? – Raystafarian Sep 6 '16 at 19:33

Add a third column to your master list titled RandomFilter

Enter this formula and copy down for each row you have with data:


The MATCH() function checks whether each employee number in your master list appears in your list of random numbers. IF() and IFERROR() tells Excel what to do if there is a match.

You will need to adjust the formula so that instead of B2 you enter the cell reference for the top employee number in your master list, and instead of RandomEmployeeNumbers!$A$1:$A$100 you enter the range of cells where your 100 random numbers are. Remember to use $ so the formula doesn't break when you copy down.

Then simply filter to only display rows saying Show.

| improve this answer | |
  • 1
    Good answer, it could be read that the "master" has names and numbers in the same cell. – Raystafarian Sep 6 '16 at 19:34
  • Good point - I read the question as saying there were two columns in the master list. If I'm wrong the OP is welcome to correct me :) – Andi Mohr Sep 7 '16 at 8:26
  • Yeah, that's how I read it too – Raystafarian Sep 7 '16 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.