Excel Picture Lookup: 5 easy steps for dynamic images

in today's video I'm going to show you how you can do lookups on pictures now I'm filming this from Majorca in Spain it's a very nice island in the Mediterranean Sea with spectacular views and beautiful scenery you get to see the hills cute little villages and great food there talking about pictures in the previous video we saw an example of the picture look up in the FIFA World Cup tool that visualized the countries which made it to the round of 16 then quarter-finals and so on now if you missed that video I'm gonna put a link to it in the description below the video now what was great about that visualization is that when the country changes the flag updates accordingly now there's no VBA here it's just smart use of Excel features now the best thing is that you can use it for different purposes you can use it for employee pictures you can use it for sales managers business unit or company logos now anywhere where you need to do lookup on pictures this technique comes in handy so let me show you how you can set this up on this sheet I have a list of sample countries along with their Flags organized like this where the aim is to create a drop-down list on the report tab right here where I'm going to be selecting the country and on top of my report here I want to see the flag of the country that I selected so my first step is to organize my images in the proper way and the proper way is that each single image should sit inside a cell I mean sitting on top of the cell but it should be surrounded by the cell and it should be right beside the correct text in our master data now one thing I do here is to click on any of these images and then press ctrl a to select all the remaining images then I'm going to press ctrl one to bring up the properties and I'm going to go to the prop tab right here and select don't move or size with cells this makes sure that if I change the row height of any of these that my image is not going to stretch now the next thing to do is to make sure that they all have the same row height and that the roll is big enough for the image to fit inside it so I'm just gonna right mouse click go to a row height and just go with 16.1 I think that will fit my images so this image does fit well here now the next thing I need to do is to make sure each of these single images is going to be inside its own cell so I'm gonna bring Australia up to where I want it and then use ctrl a to mark all of them then go to format go to a line and distribute them vertically then I'm also gonna align them on the left-hand side now let's just take a look they look quite good so each of these looks like it's in the cell now as the next step I'm gonna create my drop-down so I'm gonna go to data data validation select lists from here and as source say it's equals to b3 to be a so that's where I have the list of my countries now if I select Germany I want to see the German flag right here this means that I actually need an image to start from so I'm just gonna pick any of these images let's pick Germany ctrl C go back to report and ctrl V right here now obviously this is not dynamic right since I haven't done anything to make it dynamic if I select France this is still gonna show Germany but the idea is that I want to link this picture I have the option to type a formula in here but I'm very restricted to using cell references only or names from name manager so I can't write a vlookup formula in the formula box now check this out though if I select the image and go directly to the formula box and then click on this cell what do you think I'm gonna see I'm gonna see France now if I change this to England I'm gonna see England so basically I'm getting a dynamic image of myself alyou now what happens if instead of looking at c2 I go to master and I look at cell a3 what do you think I'm gonna see now I'm gonna see the German flag so basically this formula this link is showing me what is on top or inside the cell now this is the part that I want to make dynamic because if it's England I want it to show what is inside cell a6 if it's Germany it should be inside cell a3 now since I can't use formulas in here directly I need to go about this in a different way and that's using names from name manager so my next step is to give the cells that my flags are sitting on names and those names are going to be identical to the names that I have in front so basically cell a3 instead of it being called a three it should be called Germany I said this is a way of bookmarking the cell and I am with presenter the next cell should be called France now there is a quicker way of doing this I'm just gonna press ctrl-z to go back the quick way is to highlight this area go to formulas to the part where name manager is and use create from selection so notice that I selected both columns here now I can say create names from values in which column I don't want the top row I want the right column so basically the name that I see in the cell is going to become the name off the cell that's on the left hand side I'm gonna say okay and now check this out cell a3 is called German cell a4 is called France Spain and so on if I go to name manager I can actually see their names created automatically here now if I go back to my first tab and instead of saying master a3 I would say equals England what do you think I'm gonna see the English flag now I can use their names here but the problem is that I can't use any vlookup here I actually want to say equals what I see in c2 right here but it should translate this to a bookmark basically to an address the way to do that is with the indirect function the indirect function can take whatever it sees in a cell and translate it to an address I have a separate video for indirect I'm gonna add that to the description of this video make sure you check it out if you want to learn more about indirect now the problem I get is that I can't directly input indirect in here because remember the formula box here is a direct link either to a cell or as we saw before to a name so basically all I have to do is to create a name that says equals indirect c2 I'm just gonna press escape to leave I'm gonna go back to name manager click on new give this a new name so I'm gonna call it flag and I'm gonna paste in my formula then I copied before and the formula is very simple it equals indirect c2 is where I have made drop-down and click on OK the new name has been added here I'm going to close now the last step is to connect this instead of c2 to the flag so make sure your image is activated let's go to the formula box type in equals input flag and now we have our flag so if I change the selection here I see France so I'm gonna change the size I want my flag to be much bigger and notice that you get that border of the cell as well so I'm gonna crop this image I'm just going to click on it go to format go to crop options right here and just crop it to the size that I like and that's just going to strip out that border and then click on crop again okay so now I'm gonna go to Germany I see the German flag Australia the Australian flag if you learn something new and if you like this video do give it a thumbs up and if you like what you see you want to become more advanced in Excel and haven't subscribed to this channel consider subscribing [Music]

Channel: Leila Gharani

Views: 317,574

Rating: 4.9497786 out of 5

Keywords: lookup picture, vlookup image, link picture in excel, excel picture lookup, dynamic picture in excel, excel indirect function, pictures in excel, data validation excel, XelplusVis, create from selection excel, crop excel, align excel, excel name manager, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts

Id: wlW2UKml9CY

Length: 9min 42sec (582 seconds)

Published: Tue Jun 12 2018