Prince asks if there is a way to return a Picture as the result of a VLOOKUP instead of a Value. In Episode #1485, Bill shows us one way to solve the challenge and deliver the result.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1485 – Picture Lookup Today's question is sent in by Prince.
Prince wants to do a lookup using either VBA, but it doesn't want to return a value, it wants to return a picture.
And hey, by the way, I am just getting back from Florida this morning.
Good morning that you're watching this.
Shoot me a note – Bill@MrExcel.com.
If you have questions for the podcast, I'll be looking to do another week's worth of podcast, so shoot those questions in.
All right.
So here's what we're going to do.
We are going to put the pictures right here, in these cells.
I want to make these cells taller, format row height.
Let's go with about 100.
How does that look?
Yes.
And just make them wider, all right.
Column A – you don't have to do this.
It just bugs me.
Vertical-align top.
All right.
So now, insert the pictures here.
Make sure that they fit completely inside that cell.
I'm not going to do pictures, I'm just going to do Shapes.
So there's picture number 1, and here, insert Shapes.
Picture number two, hold down the Shift key to get a nice perfect circle.
And picture number three, insert Shapes – a triangle.
And picture number four, insert Shapes.
Let's go with smiley face.
Perfect.
Okay, so we have our four pictures.
Now we want to be able to choose from this list.
So I'm going to name the list, select the list, and we'll call it ProdList.
This is on a sheet called Sheet 2.
Let's go back to Sheet 1.
This is where we're going to enter our product.
So here, we want to have a dropdown.
I do Alt+D+L for data validation.
We're going to allow a List, and the source is =ProdList.
Click OK.
All right.
So there we can choose from the list.
And over here, a secret hidden formula, =MATCH(C3,ProdList,0) equal MATCH.
Go look for that item in the ProdList comma 0 or False.
It tells me which item I chose – the second item, the third item, or the fourth item or so on.
I'm going to need that to get the picture to work.
All right.
Let's do the picture.
We're going to copy this cell first, Ctrl C to copy.
Go back to where we want the picture to display and paste, paste the picture link.
All right.
That's cool.
That's a linked picture.
And check this out, this is the formula says, hey, go to Sheet 2, exclamation point, B2.
=Sheet2!$B$2.
Well, we want to replace that with something a little bit more fancy, but we're not allowed to put a formula here, but we can put named range in there.
So check this out.
We're going to go to Formulas, Define Name.
I'm going to call it, MyPic.
MyPic is equal to Sheet2!$B$1.
All right.
So we're going to point to that heading, but I want to move down from there, so we’re going to use the offset function.
OFFSET(Sheet2!$B$1).
And the offset says, hey, from that B1 cell, we want to move down some number of cells.
How many cells do we want to move down?
Oh, well.
That's found on Sheet 1, OFFSET(Sheet2!$B$1),Sheet1!$E$3,0).
And then, how many columns over?
No columns over so, OFFSET(Sheet2!$B$1),Sheet1!$E$3,0).
That is now a defined name.
MyPic will go to Sheet 2 starting cell B1, and go down the number of cells that we've indicated here.
Click OK, all right.
Now, check this out.
I click back on the linked picture.
It has a formula.
I'm going to erase that formula but I’m going to say, =MyPic, Enter.
Oh, look at that.
It already worked.
So we choose from the list, A101, we get the first picture, and so on.
Pretty wild trick.
Tough to set up that offset function.
I actually practiced once before the podcast.
You need to make sure to get all the dollar signs in there and the whole thing.
Matter of fact, let's look at that one again because that definitely is the most difficult part.
So = OFFSET(Sheet2!$B$1),Sheet1!$E$3,0) – equal offset.
Make sure to specify the sheet - dollar sign B dollar sign 1 and then, number of rows down.
We're going back to Sheet 1.
Even though this formula kind of lives on Sheet 1, it's not actually 1, it's on that picture.
So we want to make sure that we're explicitly saying, hey, go to Sheet 1, cell E3.
Cool, cool way to go.
All right.
Hey, I want to thank everyone for stopping by.
Remember to send in your questions to Bill@MrExcel.com.
Watch out for the podcast this upcoming week.
And I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1485 – Picture Lookup Today's question is sent in by Prince.
Prince wants to do a lookup using either VBA, but it doesn't want to return a value, it wants to return a picture.
And hey, by the way, I am just getting back from Florida this morning.
Good morning that you're watching this.
Shoot me a note – Bill@MrExcel.com.
If you have questions for the podcast, I'll be looking to do another week's worth of podcast, so shoot those questions in.
All right.
So here's what we're going to do.
We are going to put the pictures right here, in these cells.
I want to make these cells taller, format row height.
Let's go with about 100.
How does that look?
Yes.
And just make them wider, all right.
Column A – you don't have to do this.
It just bugs me.
Vertical-align top.
All right.
So now, insert the pictures here.
Make sure that they fit completely inside that cell.
I'm not going to do pictures, I'm just going to do Shapes.
So there's picture number 1, and here, insert Shapes.
Picture number two, hold down the Shift key to get a nice perfect circle.
And picture number three, insert Shapes – a triangle.
And picture number four, insert Shapes.
Let's go with smiley face.
Perfect.
Okay, so we have our four pictures.
Now we want to be able to choose from this list.
So I'm going to name the list, select the list, and we'll call it ProdList.
This is on a sheet called Sheet 2.
Let's go back to Sheet 1.
This is where we're going to enter our product.
So here, we want to have a dropdown.
I do Alt+D+L for data validation.
We're going to allow a List, and the source is =ProdList.
Click OK.
All right.
So there we can choose from the list.
And over here, a secret hidden formula, =MATCH(C3,ProdList,0) equal MATCH.
Go look for that item in the ProdList comma 0 or False.
It tells me which item I chose – the second item, the third item, or the fourth item or so on.
I'm going to need that to get the picture to work.
All right.
Let's do the picture.
We're going to copy this cell first, Ctrl C to copy.
Go back to where we want the picture to display and paste, paste the picture link.
All right.
That's cool.
That's a linked picture.
And check this out, this is the formula says, hey, go to Sheet 2, exclamation point, B2.
=Sheet2!$B$2.
Well, we want to replace that with something a little bit more fancy, but we're not allowed to put a formula here, but we can put named range in there.
So check this out.
We're going to go to Formulas, Define Name.
I'm going to call it, MyPic.
MyPic is equal to Sheet2!$B$1.
All right.
So we're going to point to that heading, but I want to move down from there, so we’re going to use the offset function.
OFFSET(Sheet2!$B$1).
And the offset says, hey, from that B1 cell, we want to move down some number of cells.
How many cells do we want to move down?
Oh, well.
That's found on Sheet 1, OFFSET(Sheet2!$B$1),Sheet1!$E$3,0).
And then, how many columns over?
No columns over so, OFFSET(Sheet2!$B$1),Sheet1!$E$3,0).
That is now a defined name.
MyPic will go to Sheet 2 starting cell B1, and go down the number of cells that we've indicated here.
Click OK, all right.
Now, check this out.
I click back on the linked picture.
It has a formula.
I'm going to erase that formula but I’m going to say, =MyPic, Enter.
Oh, look at that.
It already worked.
So we choose from the list, A101, we get the first picture, and so on.
Pretty wild trick.
Tough to set up that offset function.
I actually practiced once before the podcast.
You need to make sure to get all the dollar signs in there and the whole thing.
Matter of fact, let's look at that one again because that definitely is the most difficult part.
So = OFFSET(Sheet2!$B$1),Sheet1!$E$3,0) – equal offset.
Make sure to specify the sheet - dollar sign B dollar sign 1 and then, number of rows down.
We're going back to Sheet 1.
Even though this formula kind of lives on Sheet 1, it's not actually 1, it's on that picture.
So we want to make sure that we're explicitly saying, hey, go to Sheet 1, cell E3.
Cool, cool way to go.
All right.
Hey, I want to thank everyone for stopping by.
Remember to send in your questions to Bill@MrExcel.com.
Watch out for the podcast this upcoming week.
And I’ll see you next time for another netcast from MrExcel.
