Not able to resolve the following using Index and Match. Column displays Blank or shows nothing

[NimishK]

Hi
Not able to resolve the following using Index and Match. My column displays blank

Full code is at following : https://www.extendoffice.com/documents/excel/3354-excel-search-multiple-sheets-workbooks.html

First finding a value which is in coulmn 3 of mutiplie sheets i am able to get the value of Xfound.value with the above link
Simultaneoulsy Now want to find the corresponding value of xFound.value in column 6 in the same row but displays blank or shows nothing in .cells(xrow.5) column

rather added the following(in Bold) for index match in the above code reference link

Sub SearchFolders()
'UpdatebyKutoolsforExcel20151202
Dim _ _ _ _ _ _ _ _ _ _ _ _ _ _  _ _ _ _
Dim Lookup_Range As Range
Dim fRange As Range
Dim fCellValue As Variant

                Set Lookup_Range = xWk.Range("A1:A" & lstRow)
                Set fRange = xWk.Range("F1:F" & lstRow)

         If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                     
[B]                     With Application[/B]
[B]                     fCellValue = .Match([xfound.value], xWk.Range("C1:C" & lstRow), 0)[/B]
[B]''''''instead of Xfound.value also i tried individually .Match("B445A12")  --------- stil column 5 dispalys blank[/B]
[B]                    .Cells(xRow, 5) = .Index(fRange, .Match([xfound.value], Lookup_Range, 0))[/B]
[B]'''''WHy my .cells(xRow,5) column showing blank[/B]

[B]                     End with[/B]
End if

Thanks
Nimish

 

[NimishK]

With few more efforts i Am Getting Error as #Name in the column.
As I changed the following for clarity

fcellValue to Var1 and added Var2 and removed With.Application..... End with

Dim Var1 As Variant
Dim Var2 As Variant

Var1 = Application.Match([xfound.value], xWk.Columns(3), 0)
Var2 = Application.Match(xWk.Range("F:F"), xWk.Range("A1:J1"), 0)
.Cells(xRow, 5) = Application.Index(xWk.Columns(6), Var1, Var2)

So my xfound.Value is in column 3(text/String) and want to display its Corresponding value(Numeric) in column 6 in the same row
So not able to display the correposonding value in column 6 but #Name is Displayed

Pl help with correct syntax its Confusing

Thankx
NimishK

 

[NimishK]

Thankx Have Manage to resolve it.