Three names in 3 adjacent cells - search through a lookup table to find those exact three cells in sequence. This episode shows an array formula to solve the problem. Also - check out our new theme from Excel guru Szilvia Juhasz.
Transcript of the video:
Hey, there you go, what a catchy tune to get caught in your head, I want to thank Szilvia Juhasz for that amazing new theme song and animation.
A lot of good sports in there: Chris and… Rick, Jordan and Oz from Excel TV.
Great music.
I've heard that music for a while now – Szilvia, Mike and Val.
And illustrations by Chris, what a cool theme song.
So, thanks to them for putting that together and Szilvia, the creative force behind it.
Remember Szilvia from Santa Baby and Szilvia is amazing in Excel as well.
So hey, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 165.
Lookup to find 3 cells in a row.
So, here's a doozy sent in from YouTube: is there a way to have Excel take 3 values from 3 separate cells (side by side): Jo, Mo and Bo, and search for values in a matching list?
They have to be in 3 consecutive cells.
So, we want to find these 3 names somewhere in that list.
Check this out, we’re going to do =MATCH.
What we are going to look up?
We are going to look up the concatenation of these 3 cells, all right.
And where are we going to look?
This is crazy, that this works, but sure enough it works.
My Lookup array is going to be this list here: that starts in row 7, join with: this list here, that starts in row 8, and that has to go down one below our data.
Join with: this list here that starts in row 9 and it goes down to row below the data.
And we want “,0” for an exact match =MATCH(A11&B11&C11,E7:E25&E8:E26&E9:E27,0).
Of course that didn't work.
F2, press Ctrl+Shift+Enter though, to force it to be an array, and it finds a Jo, Mo, Bo right there.
Number two: let's just try something else.
We'll copy these three cells and Paste Special, Transpose, click OK, and it finds it in the list.
Wild, wild, wild, that you can build a Lookup table using concatenation and Excel is the right thing.
All right, Mike let's see what you have.
Mike: MrExcel, that is unbelievable.
That is the greatest point in all 165 Duels that's ever been assigned.
I cannot believe it, what is so amazing about this, is you took boom, boom, boom: 3 different starting positions, F9, so it's all one thing.
For each one of these it's 3 in succession.
And right there, boom, one thing.
And, the thing about your solution, it’s so amazing, is: it works no matter, if there are duplicates.
Here it is: the first time in Dueling Excel history I do not have a solution.
I do not have a way to do it.
I've been trying and trying, and I don't have a way to do it.
I tried to go boom, boom, boom and show all 3 numbers in the cell, but guess what?
It doesn't work when there's duplicates.
If I put Jo here, it doesn't work, it's showing 1, 3, 4 and that would tell me: oh, it's not in succession.
But right below it, it is.
Ctrl+Z, and then I tried something ridiculous like this and that doesn't work without duplicates either.
So, what am I going to do?
Nothing original, you get all of the points ever awarded for a Duel.
I'm just going to redo yours, because it's so amazing.
=MATCH(B6&B7&B8, -you did something really cool, you did this all with arrows, I love this Ctrl+Shift+Down arrow- D7:D25& -and then you did this one, and I couldn't believe this.
Watch: Ctrl+Shift+Down arrow- D8:D25 -and then you did something secret: you let go of the Ctrl key and did Shift+Down arrow to get one below.
That is like magic.
And then you went through the third one in a list: Ctrl+Shift+Down arrow- &D9:D25 -you let go of Ctrl and boom, boom with the Arrow, key, “,0”.
Ctrl+Shift+Enter, that is amazing.
All right, I'm throwing a hundred and sixty-five duel points back to you, MrExcel.
Bill: 165 points, all right, hey, thanks Mike, that was way too generous.
And you know, I have to love that Mike can detect the keyboard shortcuts, that I clearly do not even realize that I'm doing.
Ctrl+Shift+Down arrow, Ctrl+Shift+Right arrow, let go of the Ctrl key and go-to-more using just the Shift key.
That's one of those things that's built in, like I don't even think about doing it.
It reminds me of when I'm out doing seminars, I occasionally run into people, you know, so you do a little formula here and you copy that formula down, and I always show that by double-clicking the fill handle, right?
But then you get the people who are like; oh, no, no, that takes way too much time to reach for the mouse and they can just, without even thinking about it, do Ctrl+C, Left, Ctrl… shoot, I can't even do it… Ctrl+Down arrow, Right, Ctrl+Shift+Up arrow, Ctrl+V.
But they can do it like lightning-fast.
We actually should have a contest, where people send in their YouTube videos and have doing that very trick, just to see who can do it.
You don't, you must not have a GoPro and like slow it down, to watch how fast that is, all right.
Well, hey, Mike that was a fun Dueling Excel podcast and I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
Hey, let's roll that video from Szilvia one more time.
Thanks to Szilvia Juhasz for this great Dueling Excel theme video.
A lot of good sports in there: Chris and… Rick, Jordan and Oz from Excel TV.
Great music.
I've heard that music for a while now – Szilvia, Mike and Val.
And illustrations by Chris, what a cool theme song.
So, thanks to them for putting that together and Szilvia, the creative force behind it.
Remember Szilvia from Santa Baby and Szilvia is amazing in Excel as well.
So hey, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 165.
Lookup to find 3 cells in a row.
So, here's a doozy sent in from YouTube: is there a way to have Excel take 3 values from 3 separate cells (side by side): Jo, Mo and Bo, and search for values in a matching list?
They have to be in 3 consecutive cells.
So, we want to find these 3 names somewhere in that list.
Check this out, we’re going to do =MATCH.
What we are going to look up?
We are going to look up the concatenation of these 3 cells, all right.
And where are we going to look?
This is crazy, that this works, but sure enough it works.
My Lookup array is going to be this list here: that starts in row 7, join with: this list here, that starts in row 8, and that has to go down one below our data.
Join with: this list here that starts in row 9 and it goes down to row below the data.
And we want “,0” for an exact match =MATCH(A11&B11&C11,E7:E25&E8:E26&E9:E27,0).
Of course that didn't work.
F2, press Ctrl+Shift+Enter though, to force it to be an array, and it finds a Jo, Mo, Bo right there.
Number two: let's just try something else.
We'll copy these three cells and Paste Special, Transpose, click OK, and it finds it in the list.
Wild, wild, wild, that you can build a Lookup table using concatenation and Excel is the right thing.
All right, Mike let's see what you have.
Mike: MrExcel, that is unbelievable.
That is the greatest point in all 165 Duels that's ever been assigned.
I cannot believe it, what is so amazing about this, is you took boom, boom, boom: 3 different starting positions, F9, so it's all one thing.
For each one of these it's 3 in succession.
And right there, boom, one thing.
And, the thing about your solution, it’s so amazing, is: it works no matter, if there are duplicates.
Here it is: the first time in Dueling Excel history I do not have a solution.
I do not have a way to do it.
I've been trying and trying, and I don't have a way to do it.
I tried to go boom, boom, boom and show all 3 numbers in the cell, but guess what?
It doesn't work when there's duplicates.
If I put Jo here, it doesn't work, it's showing 1, 3, 4 and that would tell me: oh, it's not in succession.
But right below it, it is.
Ctrl+Z, and then I tried something ridiculous like this and that doesn't work without duplicates either.
So, what am I going to do?
Nothing original, you get all of the points ever awarded for a Duel.
I'm just going to redo yours, because it's so amazing.
=MATCH(B6&B7&B8, -you did something really cool, you did this all with arrows, I love this Ctrl+Shift+Down arrow- D7:D25& -and then you did this one, and I couldn't believe this.
Watch: Ctrl+Shift+Down arrow- D8:D25 -and then you did something secret: you let go of the Ctrl key and did Shift+Down arrow to get one below.
That is like magic.
And then you went through the third one in a list: Ctrl+Shift+Down arrow- &D9:D25 -you let go of Ctrl and boom, boom with the Arrow, key, “,0”.
Ctrl+Shift+Enter, that is amazing.
All right, I'm throwing a hundred and sixty-five duel points back to you, MrExcel.
Bill: 165 points, all right, hey, thanks Mike, that was way too generous.
And you know, I have to love that Mike can detect the keyboard shortcuts, that I clearly do not even realize that I'm doing.
Ctrl+Shift+Down arrow, Ctrl+Shift+Right arrow, let go of the Ctrl key and go-to-more using just the Shift key.
That's one of those things that's built in, like I don't even think about doing it.
It reminds me of when I'm out doing seminars, I occasionally run into people, you know, so you do a little formula here and you copy that formula down, and I always show that by double-clicking the fill handle, right?
But then you get the people who are like; oh, no, no, that takes way too much time to reach for the mouse and they can just, without even thinking about it, do Ctrl+C, Left, Ctrl… shoot, I can't even do it… Ctrl+Down arrow, Right, Ctrl+Shift+Up arrow, Ctrl+V.
But they can do it like lightning-fast.
We actually should have a contest, where people send in their YouTube videos and have doing that very trick, just to see who can do it.
You don't, you must not have a GoPro and like slow it down, to watch how fast that is, all right.
Well, hey, Mike that was a fun Dueling Excel podcast and I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
Hey, let's roll that video from Szilvia one more time.
Thanks to Szilvia Juhasz for this great Dueling Excel theme video.
