Find value in string and return reference cell

lbdgolfer

New Member
Joined
Oct 18, 2017
Messages
2
I have been a long time viewer of this forum but just recently signed up. Based on what I have found on here I know that someone has an answer to this problem.
I have a cell in column B that contains a string of values (example 1) on one sheet in a workbook. On another sheet I have cells that contain the single values found in column B. I am trying to return the value in Column A when a match if found in Column B. I can use the Find function to determine starting position but I haven't found a way to find the row reference after finding the value in column B. Assume that the value in column B only occurs once across all rows. 'Value 1' will only be in one cell in one row.

Match value
Value 1
Value 2
Value 3
etc...


Example 1
[TABLE="width: 323"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Ref1[/TD]
[TD]Value 1, Value 2, Value 3, Value 10[/TD]
[/TR]
[TR]
[TD]Ref2[/TD]
[TD]Value 4, Value 5, Value 9, Value 15[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This macro will return the value from column A to column B in the first sheet. Change the sheet names to suit your needs.
Code:
Sub FindVal()
    Application.ScreenUpdating = False
    Dim Val As Range
    Dim foundVal As Range
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each Val In Sheets("Sheet1").Range("A2:A" & LastRow)
        Set foundVal = Sheets("Sheet2").Columns("B").Find(Val, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            Val.Offset(0, 1) = foundVal.Offset(0, -1)
        End If
    Next Val
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Thanks for the quick reply. I'm not the best at macros but was able to figure out the changes necessary to match my current sheets.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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
Back
Top