Holly from Vero Beach gets a report each week with Member ID. She needs to pull in the Member Name from last week's report. This involves doing a VLOOKUP from one workbook to the other - Holly's employers is on Excel 2016 instead of 365.
Transcript of the video:
VLOOKUP names from last week's workbook into this week's workbook.
I was down in Vero Beach, Florida, and Holly came up to me in the seminar and said, "All right. I got a member ID each week along with a lot of other data and I need to go fill in the member name from last week's report.
“How can I do that?" When I'm doing these live seminars, it's great because I can always just go out to YouTube and point them directly to this video. But I was surprised, I haven't done this one.
I have a lot of VLOOKUP videos, but nothing about going to another workbook.
So let's just walk through this for Holly. Here's the report that she gets this week.
Over here on the left-hand side is member ID, a lot of other data.
All right?
Last week, so I'm going to go to View, Switch Windows, last week's report. You see, here's last week's report.
It has member number and member name. All right?
Every week, Holly brings those member names forward.
How can we just do that automatically?
Alright, so the important thing is to have both workbooks open at the same time.
If you really wanted to, you could View, Arrange All, maybe Horizontal, like that, so you could see them at the same time.
That's not required.
Alright, right here, it looks like the member name has to be inserted between head four and head five, so I insert a column here, and we do name.
All right.
Now, Holly does not have Microsoft 365 back in Excel 2016, so there is no XLOOKUP there.
So we're going to build a VLOOKUP, so equal VLOOKUP. What are we looking up?
We're looking up the member number in A2, comma. All right.
Then, as we look at that old data, we see that we have one, two, three, four, five, and six columns wide. So I start here.
Control+Shift+Down arrow to select to the bottom, and then just with the shift key held down, I'm going to one, two, three...
Press the right arrow key enough times to get over to the name.
I kind of count that this is column one, column two, column three, column four, column five, column six.
You'll see that I don't have to press F4 key because I'm going out to another workbook.
So just comma.
And they say, "Which column do you want?" I want column number six.
Then I want an exact match, which is the word False or zero out there.
All right. Now, focus down here.
I'm still working in this workbook, the new workbook for this week.
I need to put a closing parenthesis there and then Enter and then double click and copy that down.
I think we're done, so we want to go back to full screen view on our new report.
We look down through here and, right there, that member, 9770 was not there last week.
Right? You always want to go look for these #N/As.
If it was a big data set, more than one screen full of data, then you might go to the extra step of saying, "IFNA, open paren, and then out here, new member...
Or maybe something like this, so you'll see it. New member.
All right.
Then you can look for the things with the exclamation points of new member.
Go figure out what the name of that person is. I checked out it's Zeke.
Right, so fill that in.
Then, we probably want to lock those formulas as values. We select the formulas on the Home tab.
We're going to copy and then paste and choose this one here, which is called paste values.
All right. Now we're good to go.
This becomes the new report until next week when we get another one without a name and then this will become the report that we're looking up into.
All right, so thanks to Holly for showing up in the seminar and asking that question.
I promised her that I would create a video that she could refer back to, and that is it. Want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
I was down in Vero Beach, Florida, and Holly came up to me in the seminar and said, "All right. I got a member ID each week along with a lot of other data and I need to go fill in the member name from last week's report.
“How can I do that?" When I'm doing these live seminars, it's great because I can always just go out to YouTube and point them directly to this video. But I was surprised, I haven't done this one.
I have a lot of VLOOKUP videos, but nothing about going to another workbook.
So let's just walk through this for Holly. Here's the report that she gets this week.
Over here on the left-hand side is member ID, a lot of other data.
All right?
Last week, so I'm going to go to View, Switch Windows, last week's report. You see, here's last week's report.
It has member number and member name. All right?
Every week, Holly brings those member names forward.
How can we just do that automatically?
Alright, so the important thing is to have both workbooks open at the same time.
If you really wanted to, you could View, Arrange All, maybe Horizontal, like that, so you could see them at the same time.
That's not required.
Alright, right here, it looks like the member name has to be inserted between head four and head five, so I insert a column here, and we do name.
All right.
Now, Holly does not have Microsoft 365 back in Excel 2016, so there is no XLOOKUP there.
So we're going to build a VLOOKUP, so equal VLOOKUP. What are we looking up?
We're looking up the member number in A2, comma. All right.
Then, as we look at that old data, we see that we have one, two, three, four, five, and six columns wide. So I start here.
Control+Shift+Down arrow to select to the bottom, and then just with the shift key held down, I'm going to one, two, three...
Press the right arrow key enough times to get over to the name.
I kind of count that this is column one, column two, column three, column four, column five, column six.
You'll see that I don't have to press F4 key because I'm going out to another workbook.
So just comma.
And they say, "Which column do you want?" I want column number six.
Then I want an exact match, which is the word False or zero out there.
All right. Now, focus down here.
I'm still working in this workbook, the new workbook for this week.
I need to put a closing parenthesis there and then Enter and then double click and copy that down.
I think we're done, so we want to go back to full screen view on our new report.
We look down through here and, right there, that member, 9770 was not there last week.
Right? You always want to go look for these #N/As.
If it was a big data set, more than one screen full of data, then you might go to the extra step of saying, "IFNA, open paren, and then out here, new member...
Or maybe something like this, so you'll see it. New member.
All right.
Then you can look for the things with the exclamation points of new member.
Go figure out what the name of that person is. I checked out it's Zeke.
Right, so fill that in.
Then, we probably want to lock those formulas as values. We select the formulas on the Home tab.
We're going to copy and then paste and choose this one here, which is called paste values.
All right. Now we're good to go.
This becomes the new report until next week when we get another one without a name and then this will become the report that we're looking up into.
All right, so thanks to Holly for showing up in the seminar and asking that question.
I promised her that I would create a video that she could refer back to, and that is it. Want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
