comparing values in a column against multiple sheets and returning subsequent values from other columns

[Harry_1234]

I am looking for a more sophisticated query to compare values of a column in a sheet against multiple sheets and if the value is found return values from a different column. I was able to achieve this with iferror and vlookup but i have about 20 sheets to compare values against.

Sheet1: (Column A is where the values exist to be compared against rest of the sheets and return the values in column B/C/D depending on whichever column has the status data in that particular sheet)
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

Sheet2:

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Status[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[TD]Deployed[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 150"]
<colgroup><col span="2"></colgroup><tbody></tbody>[/TABLE]

Sheet3:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Stage[/TD]
[TD="width: 64"]Status[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]I[/TD]
[TD]Not Deployed[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]II[/TD]
[TD]Not Deployed[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1: (Final Result_
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Status[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[TD]Deployed[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Not Deployed[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Not Deployed[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

Change the name of the "Status" column if necessary.

Sub [COLOR=#0000ff]comparing_values[/COLOR]()
  Dim sh As Worksheet, sh1 As Worksheet, c As Range, i As Range, j As Range, f As Long
  Set sh1 = Sheets("Sheet1")
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    Application.StatusBar = "Processing row " & c.Row
    For Each sh In Sheets
      If sh.Name <> sh1.Name Then
        Set i = sh.Range("A:A").Find(c, , xlValues, xlWhole)
        If Not i Is Nothing Then
          Set j = sh.Rows(1).Find("[B][COLOR=#ff0000]Status[/COLOR][/B]", , xlValues, xlWhole)
          If Not j Is Nothing Then
            c.Offset(, 1).Value = sh.Cells(i.Row, j.Column).Value
            Exit For
          End If
        End If
      End If
    Next
  Next
  Application.Calculation = xlCalculationAutomatic
  Application.StatusBar = False
  MsgBox "End"
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (comparing_values) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.