Q: Compare 2 cells and index the last cell value I've a 2 sheets.I would like compare A1 of sheet2 with A:A of sheet1 and if it matches then index the value of A1-last row in B1 of 3rd sheet(So I'm stuck with the indexing part that finds the last row value).So this needs to be done for almost 1000 items.So any vba OR formula for this??


A: OK, this worked for me for a test just doing the XXX data value. You can modify it for the YYY.
 Option Explicit
 Sub CompareAndCopyData()

      Dim DataWorkSheet As Worksheet

      Set DataWorkSheet = ThisWorkbook.ActiveSheet

      With DataWorkSheet

           Dim SearchRange As Range
           Set SearchRange = .Range("E2:E5")

           Dim SearchText As String
           SearchText = .Range("E2").Value

           Dim SearchResult As Range
           Set SearchResult = SearchRange.Find(What:=SearchText)

           Dim LastColumn As Long
           LastColumn = .Cells(SearchResult.Row, .Columns.Count).End(xlToLeft).Column

           .Cells(8, "F").Value = .Cells(SearchResult.Row, LastColumn).Value

      End With

 End Sub

