Take a value from the same row where it finds a coincidence in other sheet

[acabanas]

Hi! Im new on excel and i have no idea on how to use it at all.

I wanted to search between two sheets, which I managed to do, but then I would like to search on the second sheet, 'C' column every coincidence with the H2 field on the first sheet. Every time it finds one coincidende (so for example C43 = H2) it will write the B43 (same row) from second sheet on the B2 from the first sheet. Every coincidence sent to B2 will be separated by comas. I made kind of a scheme to undertand it better.

IF: C(x) = H2
THEN: B(x) = B2

B2 at the end would look like: C40, C50, C100... <-- Replacing them obviously with their current values

Thank you so much!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this macro with a copy of your workbook.
To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub FindValues()
  Dim a As Variant, SearchVal As Variant
  Dim i As Long
  Dim s As String
 
  With Sheets("Sheet2")
    a = .Range("B2", .Range("C" & Rows.Count).End(xlUp)).Value
  End With
  SearchVal = Sheets("Sheet1").Range("H2").Value
  For i = 1 To UBound(a)
    If a(i, 2) = SearchVal Then s = s & ", " & a(i, 1)
  Next i
  Sheets("Sheet1").Range("B2").Value = Mid(s, 3)
End Sub

 

[fmoiduf]

Thanks Peter. I am not familiar with VBcode. if possible can you help me with index match formula

 

[Peter_SSs]

Thanks Peter. I am not familiar with VBcode. if possible can you help me with index match formula

Please start your own thread, don't hijack another active thread.

 

[acabanas]

Welcome to the MrExcel board!

Try this macro with a copy of your workbook.
To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub FindValues()
  Dim a As Variant, SearchVal As Variant
  Dim i As Long
  Dim s As String

  With Sheets("Sheet2")
    a = .Range("B2", .Range("C" & Rows.Count).End(xlUp)).Value
  End With
  SearchVal = Sheets("Sheet1").Range("H2").Value
  For i = 1 To UBound(a)
    If a(i, 2) = SearchVal Then s = s & ", " & a(i, 1)
  Next i
  Sheets("Sheet1").Range("B2").Value = Mid(s, 3)
End Sub

Thanks for responding, Peter!

For some reason I am getting a error saying something similar to "sub-index out of interval", and I say similar beacause I have excel in spanish, which is probably the reason of this. In the other hand, I was expecting to get a formula so I could use it for every row, in this case I put the example of B2 and H2 but would it work with B3,B4,B5... and H3,H4,H5...?

Again, thank you so much for your help!

 

[Peter_SSs]

I was expecting to get a formula ...

Excel 2016 does not lend itself to a string concatenation formula like you need. You could try this user-defined function instead.
To implement ..
1. Right click a sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function ListValues(rData As Range, SearchVal As Variant) As String
  Dim a As Variant
  Dim i As Long
  Dim s As String
 
  a = rData.Value
  For i = 1 To UBound(a)
    If a(i, 2) = SearchVal Then s = s & ", " & a(i, 1)
  Next i
  ListValues = Mid(s, 3)
End Function

My sample data for Sheet2

acabanas.xlsm
	A	B	C
1
2		200	b
3		40	j
4		225	f
5		17	k
6		78	d
7		206	d
8		274	g
9		224	a
10		171	g
11		169	c
12		193	e
13		68	m
14		55	n
15		142	h
16		118	e
17		171	m
18		238	d
19		156	m
20		68	j
21		49	g
22		239	e
23		23	e
24		114	d
25		79	e
26		187	c
27		150	i
28		171	d
29		67	l
30		72	k
31		205	l
32		162	b
33		143	f
34		71	f
35		218	c
36		178	i
37		264	m
38		119	a
Sheet2

.. and using the function to return results:

acabanas.xlsm
	B	C	D	E	F	G	H
1
2	193, 118, 239, 23, 79						e
3	224, 119						a
4	274, 171, 49						g
5	169, 187, 218						c
6	193, 118, 239, 23, 79						e
7	68, 171, 156, 264						m
8	55						n
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=ListValues(Sheet2!B$2:C$38,H2)

 

[acabanas]

Excel 2016 does not lend itself to a string concatenation formula like you need. You could try this user-defined function instead.
To implement ..
1. Right click a sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function ListValues(rData As Range, SearchVal As Variant) As String
  Dim a As Variant
  Dim i As Long
  Dim s As String

  a = rData.Value
  For i = 1 To UBound(a)
    If a(i, 2) = SearchVal Then s = s & ", " & a(i, 1)
  Next i
  ListValues = Mid(s, 3)
End Function

My sample data for Sheet2

acabanas.xlsm
	A	B	C
1
2		200	b
3		40	j
4		225	f
5		17	k
6		78	d
7		206	d
8		274	g
9		224	a
10		171	g
11		169	c
12		193	e
13		68	m
14		55	n
15		142	h
16		118	e
17		171	m
18		238	d
19		156	m
20		68	j
21		49	g
22		239	e
23		23	e
24		114	d
25		79	e
26		187	c
27		150	i
28		171	d
29		67	l
30		72	k
31		205	l
32		162	b
33		143	f
34		71	f
35		218	c
36		178	i
37		264	m
38		119	a
Sheet2

.. and using the function to return results:

acabanas.xlsm
	B	C	D	E	F	G	H
1
2	193, 118, 239, 23, 79						e
3	224, 119						a
4	274, 171, 49						g
5	169, 187, 218						c
6	193, 118, 239, 23, 79						e
7	68, 171, 156, 264						m
8	55						n
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=ListValues(Sheet2!B$2:C$38,H2)

That's exactly what I am searching for! But, the next error pops up. I've added the code into the 'view code' of both pages to try one by one, and both at the same time and the error screen pops up, I am sorry to molest you and also that the screenshot of the error is in spanish.

 

[Peter_SSs]

I've added the code into the 'view code' of both pages

The function code should not go in the view code of either page, it should go into a standard module.

2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.

 

[acabanas]

This is what I am doing:

1. Enter the code on a module

2. I enter the function changing the second sheet 'C' number of rows

3. When I press enter, the error pops up

4. Excel marks this as the problem

 

[acabanas]

The function code should not go in the view code of either page, it should go into a standard module.



View attachment 31178

UPDATE: I solved the error by changing the "," with a ";" because for some reason it wasn't letting me execute the function.

Now I can't see the value that is returned to me and I think I know why. In your example you use numbers, but in my case, it searches by a number (H column on sheet1) as you do, but it returns a text string, for example "Calentador Atmosférico NEO-01 de 11 Litros GLP".

As I have no idea on how macros or functions work at all I cannot detect the error but I can guess it is because this code it's made so you get a string with no space breaks or something like that. If you know where the "problem" is I would appreciate it.

Thank you again!