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

acabanas

New Member
Joined
Feb 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,233
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 27, 2018
Messages
4
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
Joined
May 28, 2005
Messages
52,233
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
52,233
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

acabanas

New Member
Joined
Feb 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
    error.PNG
    8.1 KB · Views: 7

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,233
Office Version
  1. 365
Platform
  1. Windows
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.

1612388516763.png
 

acabanas

New Member
Joined
Feb 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
This is what I am doing:

1. Enter the code on a module

1.PNG



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

2.PNG



3. When I press enter, the error pops up

3.PNG



4. Excel marks this as the problem

4.PNG
 

acabanas

New Member
Joined
Feb 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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!

1.PNG
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top