Show image using Indirect and Match on activesheet from other sheet

If you want to apply lookup on image.



You have stored images of employees on sheet2 with emp id in col a and images in col b

you want to get the image on sheet1 from sheet2 for any employee id typed in cell A1

for example: sheet2 like below





sheet1 :-






step 1

Goto excel option in ribbon(Excel 2007) then click on customize -> all commands -> the choose camera and click on add

you will see camera at top of menu





then select any cell lets suppose b1 or c1 and click on camera then again select any cell and resize the camera box size

you will see a box like below





then goto -> formula->define name

create a name range give any name like i choosed imagedisplay here and type formula
INDIRECT("P!B"&MATCH(Sheet1!$A$1,p!$A:$A,0))

in above p is the sheetname (sheet in which images are stored )

B is the column from which image is to be picked

p!$A:$A- is column in which employee ids are stored

Name range snapshot below-





now click on camera box

goto fx button and type =imagedisplay see below




press enter you will see image displayed in the box then change the value in cell A1 and new image will be displayed as per employee id