The Complete Excel Course with AI Integration
Build an employee lookup in Excel with XLOOKUP
At work the answer is rarely on one sheet: employee records sit here, department and location details there. PeopleInsight, the HR workbook you build in The Complete Excel Course with AI Integration, is industry standard like every project in the course, designed after talking with people from many different industries. It joins those sheets with VLOOKUP, XLOOKUP and INDEX with MATCH, turns misses into plain messages and ends with dynamic arrays that list, filter and sort staff without a single copy and paste.
Taught by Sagnik Bhattacharya and Virginia Thorn
What you will be able to do
You will be able to:
- Bring department names and HR business partners onto the employee sheet with VLOOKUP, and fix the errors that come from a lookup range left unlocked.
- Pull city, state, region and work arrangement across with XLOOKUP, with a message for a missing code and formulas that keep working when a column is added.
- Replace a raw #N/A with a readable message through IFERROR, so a code that stops matching explains itself to whoever reviews the sheet.
- Write INDEX and MATCH on named tables, using the at sign for the current row, for lookups that are not tied to the first column.
- Join first and last names with CONCAT, the ampersand or TEXTJOIN, and split codes such as DAL-SAL-0001 with LEFT, MID and RIGHT.
- List, filter and sort staff with UNIQUE, FILTER, SORT and SORTBY, from FILTER with AND and OR conditions to a list ordered by hire date.
Projects
What you will build.
The course has 14 industry-standard projects. These two matter most here.
-
Project 06
PeopleInsight HR workbook
Employee data joined across sheets with one formula.
The build on this page: a starter workbook of employee, survey and compensation sheets with lookup sheets for departments and locations, which you turn into an employee sheet whose new columns fill themselves.
-
Project 02
Dunder Michael Paper Company employee database
Built around a staff list, it teaches the Excel tables that PeopleInsight's formulas later call by name, and how to find and remove duplicate employee IDs before a lookup depends on them.
Lessons
The lessons that matter, in course order.
The Complete Excel Course with AI Integration has 186 lessons. This page picks out 15 of them, 1 h 10 min of video. Each lesson keeps its number in the course.
Where to start
Lesson 67 Lookup functions in Excel
Start at Lesson 67 if you can already fill a formula down and lock a reference with F4: the PeopleInsight starter workbook is waiting. New to formulas? Begin with Lesson 10, and with Lesson 1 if Excel itself is new. The date lesson this path leaves out, Lesson 74, counts working days with NETWORKDAYS whenever you need it.
15 of 186 lessons · 1 h 10 min
01 Microsoft Excel fundamentals: building a foundation from scratch Absolute and mixed references with F4: the dollar signs that hold a lookup range still while the formula is filled down, the first fix the VLOOKUP lesson calls for.
- 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
01 Microsoft Excel fundamentals: building a foundation from scratch What #DIV/0!, #NAME? and #VALUE! are telling you, and IFERROR turning an error into a readable message instead of deleting the formula.
- 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) Excel tables on a staff list: a range made into a table that takes in each new employee, with styles and a total row, the structure later formulas refer to by name.
- 19 Excel tables Create and format a table, structured references and total row 8 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) Duplicate employee IDs found with a highlight rule and removed as whole rows, because a lookup stops at the first match and needs keys that are truly unique.
- 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
08 Advanced Excel functions: lookups, text and arrays PeopleInsight's lookups: VLOOKUP for department names and HR business partners, XLOOKUP for city, state, region and work arrangement, IFERROR for codes that stop matching, INDEX and MATCH on named tables, then full names joined and position codes split.
- 67 Start here Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
- 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
- 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
- 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
- 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
- 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
- 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
08 Advanced Excel functions: lookups, text and arrays Lists that keep themselves current: UNIQUE for statuses, locations and work arrangements, FILTER with AND and OR, SORT, SORT around UNIQUE and SORTBY by hire date, then a Ninja Challenge that looks up product details for an orders sheet.
- 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
- 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
- 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
- 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
Instructors
Who teaches it.
Instructor, Excel with AI
Virginia Thorn
Emmy Award winner. Teaches Excel with AI at Coding Liquids.
Questions
Asked before starting.
Which Excel course teaches you to look up employee data across sheets?
The Complete Excel Course with AI Integration from Coding Liquids, where you build PeopleInsight, an industry-standard HR workbook, from its starter file. Employee records sit on one sheet and department and location details on others; you join them with VLOOKUP, XLOOKUP and INDEX with MATCH, turn every miss into a plain message with IFERROR, then build staff lists that filter and sort themselves with dynamic arrays. The route is on this page: 15 lessons, 1 hour 10 minutes of video, with the finished workbook to check yours against, every formula in plain view and nothing to install beyond Excel.
How do I use XLOOKUP between two sheets?
Give XLOOKUP three things: the code on your own sheet, the matching column on the other sheet and the column you want back. PeopleInsight does it in The Complete Excel Course with AI Integration (Lesson 69): on the employee sheet, start XLOOKUP with the row's location code, switch to the location sheet, select its code column and press F4 to lock it, then select the column to return, such as City, and lock that too. Add "n/a" for a code with no match and 0 for an exact match, fill down, and every employee has a city. Copied across for region and work arrangement, the lookup value slides a column, so point it back at the code, as the lesson does. Select the columns in another open workbook and the same formula works between workbooks.
Why does my VLOOKUP return wrong values after I insert a column?
Because VLOOKUP counts columns: its third argument is a fixed number, so a new column in the lookup table moves what that number points at. The Complete Excel Course with AI Integration shows the break on purpose in PeopleInsight, where the department names turn to zeros (Lesson 68), then switches to XLOOKUP, which names the return column itself, keeps working when columns are added and handles a two-way lookup too (Lesson 69). INDEX and MATCH (Lesson 71) is the other cure, and it reaches columns to the left of the key, which VLOOKUP cannot do.
How do I show a message instead of #N/A when a lookup finds nothing?
Wrap the lookup in IFERROR, or give XLOOKUP its own if-not-found text. In The Complete Excel Course with AI Integration, a department code that stops matching turns the VLOOKUP columns into errors, and wrapped in IFERROR they read NOT MATCHED instead, which tells anyone reviewing the file what went wrong (Lesson 70). XLOOKUP needs no wrapper: its fourth argument returns the text you choose, "n/a" in the lesson (Lesson 69). Either way a missing record stays visible, ready to be fixed where it started.
How do I find duplicate data before a lookup goes wrong?
Check the key column first, because a lookup returns the first match it finds and ignores the rest. The Complete Excel Course with AI Integration shows two ways. On the staff list, a Duplicate Values highlight rule marks repeated employee IDs, and Remove Duplicates, set to expand the selection and judge by Employee ID, deletes the repeated rows whole (Lesson 23). In PeopleInsight, UNIQUE spills one copy of each value, every employment status or location code, into empty cells beside the table without touching the data (Lesson 75).
How do I pull up one employee's details by picking their ID?
Build a small look-up card from parts The Complete Excel Course with AI Integration teaches. Data validation gives a cell a drop-down of employee IDs (Lesson 45), with UNIQUE ready to supply a clean list of them (Lesson 75); beside it, one XLOOKUP for each detail (department, city, region, work arrangement) reads the chosen ID and returns that person's record (Lesson 69). Pick another ID and the whole card changes. When a question has many answers, such as everyone at one location, FILTER spills them all (Lesson 76).
What Excel formulas should someone in HR learn first?
Lookups, because HR data lives on several sheets. In PeopleInsight, The Complete Excel Course with AI Integration has you join records with VLOOKUP and XLOOKUP and label misses with IFERROR, build full names with CONCAT, the ampersand and TEXTJOIN (Lesson 72), split a position code like DAL-SAL-0001 into location, department and ID with LEFT, MID and RIGHT (Lesson 73) and count working days with NETWORKDAYS (Lesson 74). Dynamic arrays then keep lists by status, location or hire date current without any manual sorting.
Is there an HR workbook I can practise lookups on?
Yes: PeopleInsight, the HR workbook The Complete Excel Course with AI Integration hands you as a starter file, with employee records, an engagement survey and compensation on their own sheets and lookup sheets for departments and locations. You build along lesson by lesson, the finished workbook lets you check every formula, and Lesson 78 closes the lookups with a Ninja Challenge to try alone before the solution. With the instructor support plan, the instructor answers your written questions under each lesson for two months from the day you switch it on, with Ask AI in every lesson for those two months, and the course ends with a Certificate of Completion.
Excel with AI
See the whole course.
The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.
The same course
For another job, goal, level, topic or comparison.
By job
Finance and accounting
- Accountants and finance teams
- Bookkeepers and accounts assistants
- FP&A and budget analysts
- Auditors and fraud examiners
- Payroll administrators
- Tax accountants and advisers
- Banking professionals
- Cost and management accountants
- Financial controllers
- Credit control and receivables teams
- Accounts payable teams
- Financial planners and advisers
- Credit analysts and loan officers
- Insurance underwriters
- Insurance claims handlers
- Reconciliation analysts
- Treasury analysts
- Medical billing and coding specialists
- Fund accountants
- Billing administrators
- AML and KYC analysts
- Actuarial analysts
- Insurance agents and brokers
- Retirement planners
HR and office administration
Operations and supply chain
- Operations and project managers
- Procurement and purchasing officers
- Logistics coordinators
- Inventory controllers and storekeepers
- Supply chain analysts
- Warehouse managers and supervisors
- PMO analysts
- Import and export coordinators
- Production planners
- Demand planners
- Fleet and transport managers
- Apparel merchandisers
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
- Quantity surveyors and estimators
- Civil and site engineers
- Quality managers and engineers
- Construction project managers
- Health and safety officers
- Document controllers
- Production managers and supervisors
- Mechanical and electrical engineers
- Industrial engineers
- Planning engineers
- Sustainability and ESG analysts
- Maintenance planners
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
By goal
- Instructor support
- Certificates
- How long it takes
- Is Excel worth learning
- A retirement calculator
- A budget tracker
- A task tracker
- A KPI dashboard
- A budget vs actual report
- A sales goal tracker
- An employee database
- A chart dashboard
- A self-refreshing sales report
- A checking macro
- A Power Automate flow
- A compound interest calculator
- A SIP calculator
- An Excel test for a job
- A step-up SIP calculator
- Practice exercises
- Excel skills for a CV