macro VBA - find matching values in columns between 2 sheets and return value in another column

[tikcr]

Hello everyone,
First of all, Happy New Year!

I would like to kindly ask for your support with this one. I have 2 excel sheets - sheet1 and sheet2.

I would like to check if the values from sheet1, Column D (starting from cell D2 and all below) exists somewhere in Column S in sheet2.
If yes, then return all matching rows from sheet2, Column S, Column G and Column I into the corresponding rows of Column F, Column G and Column H in sheet1.

I'd really appreciate your support with this one!
Many thanks!

Here is the data I am using:
sheet1

workbook.xlsx
	A	B	C	D	E	F	G	H
1				Sales ID		ID	Brutto	Netto
2				1
3				100
4				500
5				800
6				955
7				55
sheet1

sheet2

workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							Brutto		Netto										ID
2							49,41		44,47										1
3							668,85		601,96										1
4							58,24		52,42										25
5							9,8		8,82										36
6							0,68		0,61										15
7							300,55		270,49										55
8							3,99		3,59										22
9							351,21		316,09										55
10							5803,05		5222,74										100
11							4994,94		4495,45										1
12							42,67		38,4										65
13							26,4		23,76										90
sheet2

 

[DanteAmor]

then return all matching rows from sheet2, Column S, Column G and Column I into the corresponding rows of Column F, Column G and Column H in sheet1

How do you want the result on sheet1?
For example, for the first ID: 1, in sheet2 you have three records, how do you want those 3 records in sheet1, you can put the example of the desired result.

 

[tikcr]

Hi DanteAmor, thanks for your support.
I think the best would be to first look for first value from cell D2 (sheet1), if it is also in column S of sheet2, then return all those values.
Once it's done, check the next cells - D3, D4, etc.

Here is an example of how the results may be presented:

workbook.xlsx
	A	B	C	D	E	F	G	H
1				Sales ID		ID	Brutto	Netto
2				1		1	49,41	44,47
3				100		1	668,85	601,96
4				500		1	4994,94	4495,45
5				800		100	5803,05	5222,74
6				955		55	300,55	270,49
7				55		55	351,21	316,09
sheet1

 

[DanteAmor]

Try this:

Sub find_matching_values()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, r As Range, f As Range
  Dim cell As String
  Dim i As Long
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set r = sh2.Range("S:S")
  i = 2
  
  For Each c In sh1.Range("D2", sh1.Range("D" & Rows.Count).End(xlUp))
    Set f = r.Find(c.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        sh1.Range("F" & i).Value = sh2.Range("S" & f.Row).Value
        sh1.Range("G" & i).Value = sh2.Range("G" & f.Row).Value
        sh1.Range("H" & i).Value = sh2.Range("I" & f.Row).Value
        i = i + 1
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
  Next
End Sub

 

[tikcr]

Thank you very much! I will do some more tests later on, but on a first sight this is what I needed!
Really appreciate your help.