How to Offset Index Match result in Excel VBA

Question

If Sheet2!A2 value is found within a string on Sheet1!A:A, then Sheet1!"B2" = Offset(Sheet2!"A2",0,1) I.e., I have a range of values on Sheet2 that are a part of values in a range on Sheet1. If a value on Sheet2 is found in the range of values on Sheet1, then I want my result to be the adjacent value on Sheet1 to be the adjacent value from Sheet2. How is this best accomplished in VBA?

This is the code I need to incorporate the formula into: The code is now functional

Sub ListAllFiles()

    Dim objShell As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
       Set ws = Sheets("Sheet1")
    Dim myExt1 As String
    Dim myExt2 As String
        myExt1 = "pptx"
        myExt2 = "pdf"

    Set ws = Sheets("Sheet1")
    Set objShell = CreateObject("Shell.Application")
'Get the folder object associated with the directory
Set objFolder = objShell.Namespace("\\sitefs\www\htdocs\c13\comm\IMS\Schedules\")

    Dim objFolderItems As FolderItems
    Dim objFolderItem As FolderItem
        Set objFolderItems = objFolder.Items()

'Loop through the Files collection
Dim fOwner As String
Dim fAuthor As String
Dim dlm As String

ws.UsedRange.Clear
 ws.Cells(1, 1).Value = "The current files found in " & objFolder & " are:"
 ws.Cells(1, 2).Value = "Date Last Modified"
 ws.Cells(1, 3).Value = "Owner"
 ws.Cells(1, 5).Value = "Report Date:"
 ws.Cells(1, 6).Value = Now
 Range("A1").EntireRow.Font.Bold = True
  For Each objFile In objFolder.Items
  dlm = objFolder.GetDetailsOf(objFile, 3)
  If InStr(objFile.Name, "FINAL") Then GoTo 50
   If Year(CDate(dlm)) < Year(Now) Then GoTo 50
   If InStr(objFile.Name, myExt1) Or InStr(objFile.Name, myExt2) Then
    Dim x As Integer
    x = ws.UsedRange.Rows.Count + 1
        ws.Cells(x, 1).Value = objFile.Name
        'Link cell to file
            Cells(x, 1).Select
                Selection.Hyperlinks.Add Anchor:=Selection, Address:=objFile.Path
        ws.Cells(x + 0, 2).Value = dlm
    End If
50 Next

'Added this and it's now working
 Dim oRng As Range
   Dim cRng As Range
   x = ws.UsedRange.Rows.Count
  Set oRng = Range("C2")
  Set cRng = Range("C2:C" & x)
    oRng.FormulaArray = _
        "=OFFSET(INDEX(Sheet2!$A$2:$A$19,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Sheet2!$A$2:$A$19,a2))),),0)),,2)"
   cRng.FillDown

 Columns("A:B").Sort key1:=Range("B2"), _
      order1:=xlDescending, Header:=xlYes
    Columns.AutoFit
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set oRng = Nothing
Set cRng = Nothing 
End Sub

(Can you copy/paste your Sheet1 and Sheet2 tables? It will help us put the data into Excel to help you!) — BruceWayne
@ScottCraner OP is saying this is the code that imitates the formula as you asked below. Not literally trying to incorporate it into the code. — findwindow
@findwindow as I read through the code, the OP is loading sheet1 column A with links to files. That is what the code is currently doing. Now the OP wants to figure out how to match a key word in the names of those files to a table in another spread sheet and return the value in column B of the spread sheet into column B of sheet 1. That part does not exist in the current code above. And No the >0 is not needed, anything more than 0 will resolve to true. — Scott Craner
You could always us Worksheet.Evaluate Cells(x, 3).Value = ws.Evaluate("=INDEX(Sheet2!$B$2:$B$9,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Sheet2!$A$2:$A$9," & Cells(x, 1).Address(0,0) & "))),),0))") — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2016-05-26T14:21:35

This formula will find the values in Sheet 2 in the values in Sheet 1 and return the appropriate value:

=INDEX(Sheet2!$B$2:$B$9,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Sheet2!$A$2:$A$9,A2))),),0))

What is happening:

The inner INDEX is creating an array of TRUE/FALSE that, In this case is 8 objects.

Because as it iterates through the values on Sheet2 it either returns a number or an error.

The ISNUMBER returns a TRUE/FALSE based on that outcome.

Then the MATCH iterates through the array till it finds TRUE and returns that index to the outer Index formula.

Which then returns the value in that array which is at the index from the MATCH.

How to Offset Index Match result in Excel VBA

2 Answers