Excel - Dueling Excel - Employees by Department - Duel 139 - Episode 1788
2,117 views · Published 13 September 2013 · 7:35 · Indexed 6 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Dueling Excel - Employees by Department - Duel 139. Welcome back to another episode of Dueling Excel, where Mike Girvin from ExcelIsFun and I, Bill Jelen from MrExcel, tackle your toughest Excel challenges. In this episode, we're taking on a question from Tom in the UK who wants to create a list of employees by department. He has the department names in column A, employee names in column B, and the department names running across the top. Tom was looking for a VLOOKUP or something similar that would give him the list of employees. While I could have consulted Mike's book for a solution, I wanted to see if I could come up with a low-tech solution. So, I used the COUNTIF function to count the number of employees in each department and the MATCH function to determine where each department starts. Then, using the OFFSET function, I was able to return a list of employees for each department. However, I noticed that there were some #N/A errors in the list, which wouldn't look good if Tom wanted to print it. So, I used the CELL ERRORS AS <BLANK> option to hide these errors and make the list print perfectly. But, I knew that Tom wouldn't want those #N/A errors to show up at all, so I turned to Mike for a better solution. Mike's solution involved adding an extra column and using a combination of the COUNTIF, ROWS, and VLOOKUP functions to create a unique identifier for each employee. This allowed him to use a straight VLOOKUP to return the list of employees without any errors. He also shared a great tip about using an IF statement instead of IFERROR to avoid running unnecessary formulas. We hope you found this episode helpful and learned some new Excel tricks. Don't forget to subscribe to our channels for more Dueling Excel episodes and other Excel tutorials. Thanks for watching and we'll see you next week for another Dueling Excel podcast. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Introduction to Dueling Excel podcast (00:10) Episode 139: List of employees by department (00:21) Request for a VLOOKUP solution (00:31) Low-tech solution using COUNTIF and MATCH functions (01:18) Advanced solution using OFFSET function (02:07) Solution using IFERROR and VLOOKUP functions (02:57) Mike's solution using COUNTIF and VLOOKUP functions (07:06) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Array formula CELL ERRORS AS COUNTIF IFERROR List of employees by department Low-tech solution MATCH OFFSET Page setup Unique identifier VLOOKUP Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153609/ Tom would like to do a VLOOKUP to pull all employees for a given department. While VLOOKUP can not return multiple results, Bill and Mike come up with formulas to simulate the request.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920