Picture Lookup in Excel
Usage of Picture Lookup –
Picture Lookup Help to show Photo from the database according Name or Code. Find Picture from large database only one click. If you have large data of employee in excel with images. You want to see photo of particular selected Employee entering his code or Name. You can use Picture Lookup. Picture lookup use for multipurpose work like Identity card Printing, Job card Making, Certificate, View profile , Birthday Planner Etc. You can use formula multiple purpose.
How to Work Picture Lookup-
Video Tutorial Excel Functions Picture Lookup
Picture Lookup formula is not specially available. But some combine formula make picture lookup. To apply picture lookup use 3 formulas & functions.
Index function & Match Function
Combination of Index & match function help for cell connect to picture reference. Index & match function give lookup like vlookup function. This function use in name manager function, not in any cell.
Name Manager –
Name Manager function give picture result help of Index & Match Function.
Using Specific criteria and range View Picture with exact result match with criteria. Actually this is not picture lookup it is cell reference. Picture Lookup
show results that data available in database cell area. If half image place in given range & remaining is Place outside of range only that potion will show that Present is in only cell area.
How to Apply Picture Lookup
To Apply Picture Lookup You need Data of Name & Photo column. In the following Image Column B,C & D is our Database Sheet Sr no. Name & Photo. Cell Adjust According Image Size. You can use Different Sheet for result and Database. For better understanding we are using formula & Database in one sheet. In the column F & G is our result area. For Accurate Result follow the step accurate.
Step 1 –
Click in the cell D3. Means go to database sheet & click any image Cell. Need not click first cell. Remember click on Cell. not on Image. then Copy That.
Step 2 –
After copy Image Cell, click in the cell were you want Lookup Image. In our example click on cell G3. Then right click on mouse, move courser on paste special tab. When courser is moving on Past special then more paste option will be shown.
Move courser on last option that is Link Picture.Then click Link Picture Option.
After clicked Link Picture, copied image will be shown in selected cell. Look like in the bellow image.
Spet 3 –
Next step is link formula for Lookup. Go to Formula Tab then click on Name Manager option.Name manager window will be shown. In the new window click New… Option. Again New Name window will shown.
Formulas >> Name Manager >> New… >>
In the new Name window Give any Name what you want.
Then remove existing formula from refers to.
Then Type in refers to box
then select photo range. D3 to D6
Formula type carefully. excel not suggest steps in name manager.
Formula will look like
Then Click comma
Click Lookup cell, here Criteria of Search Name in Cell F3. Means what’s name you want search for image.
Select Name Range. If you search according code you can select criteria or range code. Here we are searching with name. Select Name range from database sheet.
Then Type comma
For exact match press zero (0)
Close the 2 bracket. Because two formula opened at beginning the formula (Index & Match).
Then Click OK.
You can Help bellow Image to better understand Index & match Function with Color Code.
Formula will show in Name Manager List. You can Apply multiple formula in Name manager.
Close the Name Manager option.
You can Edit or Delete formula.
Click on Lookup Image.
Then Click in the formula bar. Remove Existing Formula & type =
then Select Name manager formula.
Then Click another blank cell .
You will Get Result Image According Name.
If change the Name photo will change according name.
Disadvantage of Picture Lookup Funtion-
You can’t copy-Paste this formula for more result at one time. If you want multiple result repeat the same process. In other formula like vlookup if once apply need not apply again & again. Just copy & paste bellow. But Image Lookup not working like others formula.