Hello it’s #formulafriday. Time to have some #formulafun with a couple of functions to help me find values that are listed in one column of data and not the other.
We are going to use the IF, ISNA and the good old VLOOKUP functions. Here is an extract of my data set. I have Employee ID’s from 2016 in Column B and Employee ID’s from 2017 in Column C. I want to see which employees are not in the database now in 2017 that were present in 2016.
The formula we are going to use is simple even though it uses three of Excel’s in built functions.
Excel performs the VLOOKUP function, where it searches up the values in Column B to see if they are in Column C. If the values from Column B are not Column C then it returns an #N/A error. This now forms the first logical test of our IF function. If ISNA returns TRUE (an error), then our value from Column B is returned, if ISNA returns FALSE (no error as a result of our VLOOKUP formula then Excel returns a blank ” “.
In Summary. If the result of the VLOOKUP function returns an error then the value from B is displayed in Column D, if the VLOOKUP function returns a value not an error then Excel returns a blank.
Easy as that. We have now identified the Employees that are not still in the company in 2017.
Here is my formula.
Over To You!
Would you find this useful or do you have another way to do this. Share with us in the comments below.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Formula Friday Series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
