Find Matching Values from Column

Daedrix

New Member
Joined
Jun 2, 2016
Messages
5
I'm working on a spreadsheet with two columns, Column A has a list of serial numbers & Column B has a list of scanned data which contains some of those serial numbers as well as junk data all in one continuous line (ex 'ABCD1234FGHJ' where '1234' would be the relevant serial number).

I'm trying to come up with a formula that compares a cell in Column B with all of the serials in Column A to see if any of the serial numbers matches the data in there, and if so writes that serial number in a cell. Any help with how to do so would be greatly appreciated.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Is it possible to have more than one match?
If so, then what do you want to happen?
Is a VBA solution acceptable?
 
Upvote 0
All the serial numbers should be unique so there shouldn't be more than one match per cell. I'm not familiar with VBA & I'll be using a work computer so I'm not certain if it will be a viable option.
 
Upvote 0
I think it might possibly have to involve an array formula, which is not my specialty. I do not know how you would search all those values, but only return the result to a single cell like that.
I will post my VBA solution, in case you can use it. Basically, it is just creating your own function to do what you want. You do not need to know VBA to use it, it i simply "plug and play".
So, you would just put this VBA code in a General Module in your workbook:
Code:
Function FindSerial(luVal As String, luRange As Range) As String
'   Lookup a value to see if it is found in a range

    Dim cell As Range
    
    Application.Volatile

    FindSerial = ""

    For Each cell In luRange
        If InStr(luVal, cell.Value) > 0 Then
            FindSerial = cell.Value
            Exit Function
        End If
    Next cell

End Function
Then you would just use it like any other function you do in Excel, where the structure is:
=FindSerial(string you are looking in, range of cells that contain the serial numbers)

So, if you were looking in the string in cell B1, and your serial numbers were in A1:A40, your formula would look like this:
=FindSerial(B1,$A$1:$A$40)
which you could then copy down for the rest of your values in column B.

If VBA does not work for you, hopefully someone else will provide a non-VBA solution.
 
Last edited:
Upvote 0
Here's the formula version:


Book1
ABC
1SerialsScannedExtract
21234ABCD1234FGHJ1234
32345OIY5555KJGH5555
44444314159asdf314159
55555;ljk1234asdf1234
6314159Nothing#N/A
Sheet10
Cell Formulas
RangeFormula
C2=LOOKUP(2^16,FIND($A$2:$A$6,B2),$A$2:$A$6)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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