How to extract multiple values from same lookup

[nuckfuts]

Hi -

I'm looking to extract multiple Unique ID's based on a few specific Values. The condensed table below shows the source data (Sheet2). Based on Values listed in Sheet1, I'd like to extract all relevant Unique ID's.

I've considered array formulas, although I try to avoid those when possible for processing speed. I've also found ways to filter the table by the values in the cells but all solutions list out each criteria/filter/Value as a separate line of code and may be extremely repetitive considering there could be 20+ values. Is there a good way to do this?

Sheet2:

Sheet1:
Value1
Value2
Value3
...
(could be 20+ values)

 

[Fluff]

As long as you have headers in every cell A1:S1 then use

Sub nuckfuts()
   Dim Ary As Variant
   Dim r As Long
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   For r = 2 To UBound(Ary)
      Dic(Ary(r, 1)) = Empty
   Next r
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   With CreateObject("scripting.dictionary")
      For r = 2 To UBound(Ary)
         If Dic.Exists(Ary(r, 19)) Then .Item(Ary(r, 1)) = Empty
      Next r
      Sheets("Sheet1").Range("B2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

This also ignores the header in A1 on sheet1

 

[nuckfuts]

As long as you have headers in every cell A1:S1 then use

Sub nuckfuts()
   Dim Ary As Variant
   Dim r As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   For r = 2 To UBound(Ary)
      Dic(Ary(r, 1)) = Empty
   Next r
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   With CreateObject("scripting.dictionary")
      For r = 2 To UBound(Ary)
         If Dic.Exists(Ary(r, 19)) Then .Item(Ary(r, 1)) = Empty
      Next r
      Sheets("Sheet1").Range("B2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

This also ignores the header in A1 on sheet1

This is working perfectly! I tried to figure out how to adjust it myself but this is far beyond my VBA understanding - thanks!

 

[Fluff]

You're welcome & thanks for the feedback.