vlookup pictures in excel

[Olivia]

My question to you is that I want to have a column of picture name in a single column and actual picture in the similar row in the next column and after that find them by doing a vlookup on another sheet. Is it possible to do that? If yes than, it would be very beneficial for presentation point of view. The Formula is capable to see the object names?

[Vandam]

If you ask me than I will say yes it is possible. I have use the same idea in my presentation and believe me it worked fine. The presentation was perfect with picture column in single row including the vLookup on another sheet.The code for it is:

Public Sub UpdateWeather(rngTarget As Range)
Dim oPic_src As Picture
Dim oPic_update As Picture
Dim oPic_new As Picture
Dim wsReport As Worksheet
Dim wsPicLookup As Worksheet
Dim lPic As Long
Application.ScreenUpdating = False
'Set worksheets
Set wsReport = Worksheets("Report")
Set wsPicLookup = Worksheets("PictureLookup")
    With wsReport
            'Work out which picture should be updated.  (Need column of range)
            lPic = Intersect(.Range("rng_weather"), rngTarget).Column - .Range("rng_weather").Cells(1, 1).Column + 1
            
            'Set reference to pictures to replace/replace with
            Set oPic_update = .Pictures("pic_day" & lPic)
            Set oPic_src = wsPicLookup.Pictures(Application.WorksheetFunction.VLookup(rngTarget.Value, wsPicLookup.Range("tbl_weatherpics"), 2, False))
        
            'Copy picture to be used and place it where old picture resides
            Set oPic_new = oPic_src.Duplicate
            oPic_new.Cut
            .Paste
            Set oPic_new = Selection
            With oPic_new
               .Top = oPic_update.Top
               .Left = oPic_update.Left
               .Name = oPic_update.Name
            End With
              'Delete old picture
            oPic_update.Delete
    End With
     rngTarget.Activate
End Sub

[Jacques25]

I would like to add to it, to have a column of picture name in a single column you have to give proper path syntax in your code and also you have to give picture name to cell. I have typed it for you. You just have to copy paste it.

Sub pic() 
     
    Dim picname As String 
    Dim wherefrom As String 
     
     'Get picture name from cell B10 as variable 'picname'
    picname = Range("B10") ' B10 = Phreds Wedding
     
     'get drive:\dir name from cell including final "\"
    wherefrom = Range("B11") 'B11 = D:\my documents\
     
     'Use variable in string
    ActiveSheet.Pictures.Insert(wherefrom & picname & ".jpg").Select 
     
    With Selection 
        .ShapeRange.LockAspectRatio = msoTrue 
        .ShapeRange.Height = 195# 
        .ShapeRange.Width = 180# 
        .ShapeRange.Rotation = 0# 
    End With 
     End Sub

[Author]

If you want to enable yourself to vLookup pictures in excel then use the below updated formula in the module. I have got this formula after doing much research over the internet.

• =IF(AND(ISNA(VLOOKUP($I471,'confirmed list new.xls'!NAV,1,0)),ISNA(VLOOKUP($I471,'confirmed list new.xls'!NAV,1,0))),"No Match", IF(ISNA(VLOOKUP($I471,'confirmed list new.xls'!CV,1,0)), "No", "Yes"))

[Dwarner]

If the above formula don’t work for you and if your still unable to do have picture name in single column of a row then try the below options an experiment yourself, I am sure any one of them will work.

• =IF(AND(ISNA(VLOOKUP($I500,'confirmed list new.xls'!NAV,1,0)),ISNA(VLOOKUP($I500,'confirmed list new.xls'!CV,1,0))),"No Match", IF(ISNA(VLOOKUP($I500,'confirmed list new.xls'!CV,1,0)), "NAV", "CV"))
  
• =IF(AND(ISNA(VLOOKUP(I3, ListA, 3, 0)),ISNA(VLOOKUP(I3, ListB, 3, 0))),"No Match",IF(ISNA(VLOOKUP(I3, ListA, 3, 0)),"Match in List B","Match in List A"))

[Ronchi]

Generally you can use the ISNA() function to deal first with the #N/A errors that are returned by VLOOKUP. There are two options either Match or No Match to return.
Try the below for No Match:

1. =IF(ISNA(VLOOKUP(I3, ListB, 3, 0)),"No Match","Match")
   
   Try the below Match to return pictures from vLookup:
   
2. =IF(ISNA(VLOOKUP(I3, ListB, 3, 0)),"No Match",VLOOKUP(I3, ListB, 3, 0))

[Steyn]

According to me there is a simple theory and that simple theory is that if the AND returns TRUE, then both VLOOKUPs returned #N/A, so therefore there is No Match.

1. IF(ISNA(VLOOKUP(I3, ListA, 3, 0)),"Match in List B",
2. If the VLOOKUP for ListA didn't return a #N/A, then the match must be in ListA.
3. IF(AND(ISNA(VLOOKUP(I3, ListA, 3, 0)),ISNA(VLOOKUP(I3, ListB, 3, 0))),"No Match",
4. If the next VLOOKUP returns #N/A, then the match isn't in ListA, therefore it must be in ListB