Today we are going to discuss about different methods in excel to do picture lookup.
We usually do lookup for cell values using VLOOKUP, INDEX functions. But here we are going to do picture lookup, little bit tricky but possible. Let me give you insights about our requirement.
We have animals name and picture in the range B2:C5. In F3 cell, drop down is provided to select the animal's name from a list. Our requirement is to get the picture in G3 cell for the selected animal.
Make sure that pictures in C column fits inside the cell.
Lot to learn, let's dive in.
To create data validation in F3 cell,
in the data menu, data tools group, you can see the small drop down, click on that to see data validation option, click on that as shown below.
Data validation dialog box will open, ensure that settings tab is selected, otherwise select Settings tab at top right corner. Click the drop down in allow and select List option.
Then, click on the up-arrow button for source and select the range from B3:B5 and click OK. Now, if we select F3 cell, you can see that drop down option is enabled. We can only select any of the options in drop down in that particular cell, if we try to enter values other than in drop down, excel will not allow. This is the basic form of data validation with list range.
Method 1:
Use Below formula in G3 cell,
=INDEX($C$3:$C$5,MATCH($F$3,$B$3:$B$5,0))
To get clarity on INDEX and MATCH function, please visit below post.
Here, Match function feeds relative position of selected animal name into row_num argument of INDEX function. In our case, F3 cell value 'Lion' is at 2nd position in the range B3:B5, if we feed that 2 as row number argument, INDEX function will pick C4 cell in the range C3:C5.
As of now, INDEX function delivers zero as output, this is because, we don't have any data in range C3:C5.
The pictures we pasted in the range is actually not in the cell, it is placed over the spreadsheet. That's why, formula not picking the pictures, no worries, we have a work around for that.
We are going to use the formula in G3 cell in Name Manager. so please make sure that necessary reference locking's are done.
Now, copy whole formula and keep the F3 cell as active cell, then click on the 'Formulas' tab in the menu bar and select 'Name Manager'.
Click New in dialog box, 'New name' dialog box will open, in that set the name as 'Pic_Lookup' and in the refers to section paste the formula and click OK.
In Name manager dialog box, click on the refers to section, you can see that, excel shows the resulting range by moving dotted line. It means that the formula is right, so we can click close.
Formula in G3 cell is not required
anymore, so we can delete that.
Select any one cell in the range C3:C5, Let's take C3 cell. Copy that cell using shortcut key Ctrl + C, then select G3 cell. In Home tab, in left most corner, Clipboard group is there. In that group, click on the drop down in Paste option and select linked picture option at the end. When we click on the picture at G3 cell, we can see the $C$3 cell reference in formula bar.
This is because, we copied the range C3 and pasted in G3 as linked picture. In the formula bar, delete $C$3 (Keep '=' sign) and type 'Pic', when you see Pic_lookup option click tab and enter. As soon as you press enter, you can see that Elephant picture turned into Lion picture.
If we select 'Tiger' from the drop down in F3 cell, then you can see tiger picture in G3 cell.
This is dynamic classic picture lookup.
Method 2:
Select the range B3:C5 and press Ctrl + Shift + F3 shortcut key to open 'Create from Selection' dialog box. else,
Select the range B3:C3 and in the menu bar, 'Formulas' tab, 'Defined Names' group, select 'create from selection' option. You can see 'Left Column' option is checked, if not you check the box.
Now, each cell in C3:C5 range is getting named by the cell values in B3:B5.
Click on the drop down in name box, select 'Tiger' and you can see C5 cell is selected.
Use below formula in G3 cell,
=INDIRECT($F$3)
INDIRECT function converts the text reference in to range reference.
In our case, we selected 'Elephant' in F3 cell, so INDIRECT function will search the range named 'Elephant', if it finds, will return the value from that range. Otherwise, it will throw an error.
For now, output of this function is zero, this is because we don't have any value in C5 cell.
Copy the formula in G3 cell, create new name in Name manager as we did in Method1.
I used the same name 'Pic_Lookup' and deleted the formula in G3 cell.
Select any one cell in the range C3:C5, Let's take C3 cell. Copy that cell using shortcut key Ctrl + C, then select G3 cell. In Home tab, in left most corner, Clipboard group is there. In that group, click on the drop down in Paste option and select linked picture option at the end. When we click on the picture at G3 cell, we can see the $C$3 cell reference in formula bar.
This is because, we copied the range C3 and pasted in G3 as linked picture. In the formula bar, delete $C$3 (Keep '=' sign) and type 'Pic', when you see Pic_lookup option click tab and enter. As soon as you press enter, you can see that Elephant picture turned into Lion picture.
If we select 'Tiger' from the drop down in F3 cell, then you can see tiger picture in G3 cell.
This is dynamic classic picture lookup.
Comentarios