Advanced Vlookup With Multiple Criteria

Excel Video: How to Vlookup Multiple Criteria The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for just one lookup value. But what if you want to look up with several conditions? There are a few different solutions for you to choose from. Formula 1. VLOOKUP with two criteria Suppose you have a list of employees with their departments, hire dates, and statuses To retrieve the Status of John Jackson in the Finance department using VLOOKUP, you’ll want to structure your formula so it looks up both the name and department. Since we have Two people bearing John Jackson but they are in different department. Since VLOOKUP only works with one key column, the trick is to create a helper column that combines Employee Name and Department. Here’s how you can do it: Step 1: Create a Helper Column In a new column (say column A), combine the Employee Name and Department: excel = B2& " " &C2 This will give you values like John Jackson Finance. Step 2: Use VLOOKUP Now, use VLOOKUP to find the status: =VLOOKUP(H2&" "&I2,$A$1:$E$17,5,FALSE) "H2&" "&I2 (John Jackson Finance)" → the lookup value (name + department). $A$1:$E$17 → the table array (make sure it includes the helper column and the Status column). 5 → the column index number for Status (relative to your table array). FALSE → ensures an exact match. Result This will return Contract, which is the recorded status for John Jackson in Finance. Is this helpful ? Comment and share the video with your connections Do you want to become an Excel Guru? Join our October Cohort. It is a 5 weeks program. (Weekend classes) The training starts on Saturday 3rd of October, 2026 Call us on 08062468296 or visit our website at www.leadleapconsult.com to register Choose LEAD-LEAP CONSULTING LIMITED #advancedexcel, #advancedvlookup #dataanalytics #leadleap #mrexcel