Hello All. In this tutorial, let’s see how to do a picture lookup in excel. Imagine a list of name and corresponding pictures. If you select a name, the appropriate picture should list (something similar to a Vlookup / Hlookup).
In our example, we are working on the below example:
First, we need to assign the picture to the names – technically we are assigning the value of column B to A. In our example, B2 will be assigned to Sachin. We use the name manager (a.k.a name range) to achieve this. More on name range here.
Select A2:B6 -> Formulas -> Create from selection (under defined names section) -> Check only “Left Column”
Now, let’s copy one of the cells in B column (any cell between B2 to B6). We need to paste special (link to picture option) to the destination where we need the results. Please ensure you paste special. Please note that value of the pasted value still shows the cell in B column – In our example, I copied B2 and pasted in D3. Please note the value of D3 still shows as B2
Let’s create a list of names as input – a simple data validation list
Now, let’s create a new name in the name manager using Indirect function to reference values from the name column in sheet1.
Navigate to Formulas -> Name Manager -> New -> Enter a name, Reference value as “=indirect(C3)”
Remember the picture in D3 above had the reference as B2? It’s time to change that. Click on the picture and replace the value of B2 with Final1 (name of the indirect reference we added in the above step)
Now we are all set. Toggle between the values in C3 and you would see the appropriate pictures should pop up.
I hope you like this tutorial. Please let us know your feedback which would help us to improve the content. Thanks for reading.