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.
Transcript of the video:
Bill: Hey. Welcome back. It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. We’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 139, list of employees by department.
Alright. Today’s question came in from Tom in the UK.
Tom describes he has department name in column A, employee name in column B, and then the department names running across the top here.
He was looking for a VLOOKUP or something that would give him the list of employees.
Alright.
Well, I know that I could get out Mike's book and figure out how to do this but I'm going to go…just…let me see if I can figure out a way to do this low-tech.
Let's call it low-tech.
So, =, how many, COUNTIF, count how many employees are in this department, so we take all of column A. I'll press F4 because I'm going to copy that sideways.
Is = to the A up there, and so we now know how many employees are in each department, and then I need to know where does it start.
So, where does it start?
=MATCH of this letter, column A, or letter A, department A, within column A. Again, I'll press F4 there.
, 0 because we want an exact match.
It tells me that the first A is in row 2, the first B is in row 8, and the first C is in row 11.
[ =COUNTIF($A:$A,E1) ], [ =MATCH(E1,$A:$A,0) ] Now, watch this,.
I'm going to do just…I'm going to select a large range of cells, more than I would ever need, and say =OFFSET, OFFSET, starting from cell B1, F4 to lock that down, and how many rows down do I want to go?
I want to go down 2 rows.
Oh, that's one too many, but we'll lock that to the row, -1.
,. How many columns over?
0 columns over.
How tall do I want it to be?
That's the how many that we had there.
So, E2 , 1.
Alright.
So, OFFSET in this case is going to return several different answers, and because I selected a large range of cells, all I have to do is press CONTROL+SHIFT+ENTER and those answers are all going to appear in the right spot, like this.
Bam.
[ =OFFSET($B$1,E$3-1,0,E$2,1) ] Isn't that cool?
Copy, paste, paste, and I now have a list of the employees.
If I could just get them to ignore the N/As, and of course, since we're going to print this, I don't know if Tom's going to print it or not, but let's assume he's going to print it, he could just come back here and say CELL ERRORS AS <BLANK> and now it's going to print perfectly, alright, and we're good to go, but I know, of course, Tom's going to want to not have those N/As show up.
So, I said, alright.
Let's do a little IFERROR here and, of course, even though it was a valiant try, CONTROL+SHIFT+ENTER, it doesn't work because the whole thing isn't an error, or IFERROR can't deal with an array, or I don't know why.
It's just not working.
So, Mike, I know, I know that you will have an amazing solution to this.
Let's see what you have.
Mike: Thanks, MrExcel.
Hey, OFFSET and page setup don't show the error, I love it, and what's so cool about this is that's a little teeny formula compared to the array formula you'd have to do.
Now, actually, MrExcel was right.
In chapter 15 of my CTRL+SHIFT+ENTER, that chapter is called Extracting Data with Criteria, and I did a COUNTIF and this is straight from the book.
There's a big array formula with aggregate, but let's do something slightly different here.
How about adding an extra column and then doing a straight VLOOKUP?
Now, here's the extra column since this is the classic multiple values in the first column of lookup and we need to return multiple items problem, right?
So, how in the world do we deal that?
Well, since that's a duplicate, if we just create a key over here that's not a duplicate, we can use that, and here's how we will do it.
We'll say, hey, get that A and we need to join it with the & SHIFT 7 and we're going to use the COUNTIF.
Now, the trick here is we're going to do an expandable range, and, right now…whoops…right now that would not work, but B3, you lock the first cell reference.
I'm going to lock just the row reference.
That's an expandable range.
The 3 is locked here but not here, so as we copy down CONTROL+ENTER, COUNTIF only counts one A here, but the expandable range, you could see its expanded, the 3 is locked, the 4 is not, so now it's looking at both As.
When it gets down, here you could see there's a 3 and an 8.
It's counting 6, letter A. So, that's our new unique count or a unique criteria column, or let's say it a different way.
It's a unique identifier that helps us with our VLOOKUP.
[ =B4&COUNTIF(B$3:B4,B4) ] Now, the only trick when we get over to VLOOKUP is the lookup value, but no problem.
We're going to take this A. Remember, as we copy it down, we need it locked, but when we to go to the side, we need the A to move to B.
We need A1, A2, A3, so we're going to join it with a number formula incrementer.
ROWS.
We're going to use an expandable range like we just did over in COUNTIF.
F $ sign…whoops…F $ sign, and I'm sitting in 7, : F7.
That will expand as we go down.
ROWS says how many rows are there.
7 to 7.
That's one.
That'll turn to 8 then 9, so that’ll give us 1, 2, 3 as we copy it down.
,. Now, the table, we just include this first column.
F4. , the column is number 3, so a 3, and we're going to do exact match.
Now, this will give us an error just like over in MrExcel because when we copy IT down, the VLOOKUP is saying, hey, I can't find the 7th and 8th value but no problem.
[ =VLOOKUP(F$6&ROWS(F$7:F7),$A$3:$C$18,3,0) ] Now, we don't want to use IFERROR and I talked about this in my book, in other videos.
Any time you can figure out a logical test that does not involve the actual function, then you should use that with the IF and run a null text string instead of having to run the formula multiple times.
So, I'm going to choose to do IF…whoops…I'm such a bad typer.
I'm just going to copy this.
I should have copied this from before.
So, the logical test will be, hey, 1, 2, 3, 4, 5, 6, 7.
When you are > this count right here, then we're past the row we want.
We're past, in this case, A6.
Then, just show for value if true, a null text string, “”. That's the syntax for please show nothing.
Alright.
Otherwise, run the formula.
That way, when we're down here, it's not running VLOOKUP.
It’s just running a logical test and plopping a null text string in the in the cell and enter.
Now, here, it wouldn't matter at all.
This is a teeny data set, but with big data sets, it does matter significantly especially with array formulas, and it's a good habit to get in.
No problem.
In this case, small data set, IFERROR would work.
Alright.
Throw it back to MrExcel.
[ =IF(ROWS(F$7:F7)>F$4,“”,VLOOKUP(F$6&ROWS(F$7:F7),$A$3:$C$18,3,0)) ] Bill: Hey.
Alright, Mike.
That was a great way to go.
I love that concatenated VLOOKUP.
Also, I never thought about the point…I always thought that IFERROR was a really fast way to go because we didn't have to do the VLOOKUP twice.
Your point about only not having to do the VLOOKUP at all because of the logical test, that’s a great tip [ unintelligible – 07:25 ].
Alright.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel. We’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 139, list of employees by department.
Alright. Today’s question came in from Tom in the UK.
Tom describes he has department name in column A, employee name in column B, and then the department names running across the top here.
He was looking for a VLOOKUP or something that would give him the list of employees.
Alright.
Well, I know that I could get out Mike's book and figure out how to do this but I'm going to go…just…let me see if I can figure out a way to do this low-tech.
Let's call it low-tech.
So, =, how many, COUNTIF, count how many employees are in this department, so we take all of column A. I'll press F4 because I'm going to copy that sideways.
Is = to the A up there, and so we now know how many employees are in each department, and then I need to know where does it start.
So, where does it start?
=MATCH of this letter, column A, or letter A, department A, within column A. Again, I'll press F4 there.
, 0 because we want an exact match.
It tells me that the first A is in row 2, the first B is in row 8, and the first C is in row 11.
[ =COUNTIF($A:$A,E1) ], [ =MATCH(E1,$A:$A,0) ] Now, watch this,.
I'm going to do just…I'm going to select a large range of cells, more than I would ever need, and say =OFFSET, OFFSET, starting from cell B1, F4 to lock that down, and how many rows down do I want to go?
I want to go down 2 rows.
Oh, that's one too many, but we'll lock that to the row, -1.
,. How many columns over?
0 columns over.
How tall do I want it to be?
That's the how many that we had there.
So, E2 , 1.
Alright.
So, OFFSET in this case is going to return several different answers, and because I selected a large range of cells, all I have to do is press CONTROL+SHIFT+ENTER and those answers are all going to appear in the right spot, like this.
Bam.
[ =OFFSET($B$1,E$3-1,0,E$2,1) ] Isn't that cool?
Copy, paste, paste, and I now have a list of the employees.
If I could just get them to ignore the N/As, and of course, since we're going to print this, I don't know if Tom's going to print it or not, but let's assume he's going to print it, he could just come back here and say CELL ERRORS AS <BLANK> and now it's going to print perfectly, alright, and we're good to go, but I know, of course, Tom's going to want to not have those N/As show up.
So, I said, alright.
Let's do a little IFERROR here and, of course, even though it was a valiant try, CONTROL+SHIFT+ENTER, it doesn't work because the whole thing isn't an error, or IFERROR can't deal with an array, or I don't know why.
It's just not working.
So, Mike, I know, I know that you will have an amazing solution to this.
Let's see what you have.
Mike: Thanks, MrExcel.
Hey, OFFSET and page setup don't show the error, I love it, and what's so cool about this is that's a little teeny formula compared to the array formula you'd have to do.
Now, actually, MrExcel was right.
In chapter 15 of my CTRL+SHIFT+ENTER, that chapter is called Extracting Data with Criteria, and I did a COUNTIF and this is straight from the book.
There's a big array formula with aggregate, but let's do something slightly different here.
How about adding an extra column and then doing a straight VLOOKUP?
Now, here's the extra column since this is the classic multiple values in the first column of lookup and we need to return multiple items problem, right?
So, how in the world do we deal that?
Well, since that's a duplicate, if we just create a key over here that's not a duplicate, we can use that, and here's how we will do it.
We'll say, hey, get that A and we need to join it with the & SHIFT 7 and we're going to use the COUNTIF.
Now, the trick here is we're going to do an expandable range, and, right now…whoops…right now that would not work, but B3, you lock the first cell reference.
I'm going to lock just the row reference.
That's an expandable range.
The 3 is locked here but not here, so as we copy down CONTROL+ENTER, COUNTIF only counts one A here, but the expandable range, you could see its expanded, the 3 is locked, the 4 is not, so now it's looking at both As.
When it gets down, here you could see there's a 3 and an 8.
It's counting 6, letter A. So, that's our new unique count or a unique criteria column, or let's say it a different way.
It's a unique identifier that helps us with our VLOOKUP.
[ =B4&COUNTIF(B$3:B4,B4) ] Now, the only trick when we get over to VLOOKUP is the lookup value, but no problem.
We're going to take this A. Remember, as we copy it down, we need it locked, but when we to go to the side, we need the A to move to B.
We need A1, A2, A3, so we're going to join it with a number formula incrementer.
ROWS.
We're going to use an expandable range like we just did over in COUNTIF.
F $ sign…whoops…F $ sign, and I'm sitting in 7, : F7.
That will expand as we go down.
ROWS says how many rows are there.
7 to 7.
That's one.
That'll turn to 8 then 9, so that’ll give us 1, 2, 3 as we copy it down.
,. Now, the table, we just include this first column.
F4. , the column is number 3, so a 3, and we're going to do exact match.
Now, this will give us an error just like over in MrExcel because when we copy IT down, the VLOOKUP is saying, hey, I can't find the 7th and 8th value but no problem.
[ =VLOOKUP(F$6&ROWS(F$7:F7),$A$3:$C$18,3,0) ] Now, we don't want to use IFERROR and I talked about this in my book, in other videos.
Any time you can figure out a logical test that does not involve the actual function, then you should use that with the IF and run a null text string instead of having to run the formula multiple times.
So, I'm going to choose to do IF…whoops…I'm such a bad typer.
I'm just going to copy this.
I should have copied this from before.
So, the logical test will be, hey, 1, 2, 3, 4, 5, 6, 7.
When you are > this count right here, then we're past the row we want.
We're past, in this case, A6.
Then, just show for value if true, a null text string, “”. That's the syntax for please show nothing.
Alright.
Otherwise, run the formula.
That way, when we're down here, it's not running VLOOKUP.
It’s just running a logical test and plopping a null text string in the in the cell and enter.
Now, here, it wouldn't matter at all.
This is a teeny data set, but with big data sets, it does matter significantly especially with array formulas, and it's a good habit to get in.
No problem.
In this case, small data set, IFERROR would work.
Alright.
Throw it back to MrExcel.
[ =IF(ROWS(F$7:F7)>F$4,“”,VLOOKUP(F$6&ROWS(F$7:F7),$A$3:$C$18,3,0)) ] Bill: Hey.
Alright, Mike.
That was a great way to go.
I love that concatenated VLOOKUP.
Also, I never thought about the point…I always thought that IFERROR was a really fast way to go because we didn't have to do the VLOOKUP twice.
Your point about only not having to do the VLOOKUP at all because of the logical test, that’s a great tip [ unintelligible – 07:25 ].
Alright.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
