Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup using two criteria

0

I have two worksheets in a workbook, one with employee ID and Pay Code for 2016 and one with employee ID and Pay Code from 2017. Some employees that were here in 2016 are not still in company in 2017, and likewise 2017 contains new employees. I would like to do a Vlookup in the 2017 worksheet for the employee using their employee ID and compare their Pay Code to that of 2016...basically i want to look for differences. Those that are the same are ok but those that are different to 2016 I would like to highlight. See the attached for an example of the spreadsheet, plus my attempt at the formula. It was giving me back what was in the 2016 sheet for all and N/A for those in 2017 that were not present in 2016. I would like it to display just the employees that have a different code in 2017 to 2016. Help would be much appreciated as i am not great with excel!

=VLOOKUP(A3,'2016'!A2:B17,2,FALSE)

excelvlookup
MS Office Version: 2013
posted by: MelPB (rep: 2) on Wed Sep 27, 2017 at 5:00 pm
Answer
Discuss

Answers

0
Selected Answer

This formula should do what you want.

=IFERROR(IF(VLOOKUP($A3,'2016'!$A$2:$B$17,2,FALSE)=$B3,"",VLOOKUP($A3,'2016'!$A$2:$B$17,2,FALSE)),"")

There are two null strings ("") in this formula which you might want to replace with strings that say something. The first one is used when the employee was employed in the same pay group in 2016. The second one applies to new employees.

Please observe the definition of the target range on the 2016 sheet in absolute terms ($A$2:$B$17). The difference, as compared to A2:B17, is that the latter changes as you copy the formula down resulting in each formula looking for a match in a different range. It seems to work for you because the sequence of names in 2016 and 2017 was the same but, in fact, it didn't work :-)

posted by: Variatus (rep: 4889) on Wed Sep 27, 2017 at 11:36 pm
Discuss

Discussion

Thank you so much that worked great!!
MelPB (rep: 2) Sep 28, '17 at 9:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login