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

#### acabanas

##### New Member
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!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Peter_SSs

##### MrExcel MVP, Moderator
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)

VBA Code:
``````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

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

Last edited by a moderator:

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks Peter. I am not familiar with VBcode. if possible can you help me with index match formula

#### acabanas

##### New Member

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)

VBA Code:
``````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

##### MrExcel MVP, Moderator
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)

VBA Code:
``````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
ABC
1
2200b
340j
4225f
517k
678d
7206d
8274g
9224a
10171g
11169c
12193e
1368m
1455n
15142h
16118e
17171m
18238d
19156m
2068j
2149g
22239e
2323e
24114d
2579e
26187c
27150i
28171d
2967l
3072k
31205l
32162b
33143f
3471f
35218c
36178i
37264m
38119a
Sheet2

.. and using the function to return results:

Cell Formulas
RangeFormula
B2:B8B2=ListValues(Sheet2!B\$2:C\$38,H2)

#### acabanas

##### New Member

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)

VBA Code:
``````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
ABC
1
2200b
340j
4225f
517k
678d
7206d
8274g
9224a
10171g
11169c
12193e
1368m
1455n
15142h
16118e
17171m
18238d
19156m
2068j
2149g
22239e
2323e
24114d
2579e
26187c
27150i
28171d
2967l
3072k
31205l
32162b
33143f
3471f
35218c
36178i
37264m
38119a
Sheet2

.. and using the function to return results:

Cell Formulas
RangeFormula
B2:B8B2=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.

#### Attachments

• error.PNG
8.1 KB · Views: 7

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### New Member
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

##### New Member
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!

Replies
1
Views
68
Replies
4
Views
146
Replies
3
Views
376
Replies
1
Views
145
Replies
10
Views
386

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,573
Messages
5,765,182
Members
425,266
Latest member
CPAgirl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back