GetMatch plus VBA Code to Extract Matched Numbers from 2 Single Cells

Hi, This David

Please i need some help on GetMatch Formula and the VBA Macro..

Here is the Problem:

I have First Group of Number is In Cell C6 -> 28-17-20-32-21-18-25-40-50-60
the Second Group Of Numbers is In Cell C9 -> 18-19-20-21-22-23-24-40-50-60

i Need to Extract The Numbers that Match in the 2 Groups (20 21 18 40 50 60) and copy In Cell F6


Please i need a Correction

I Inserted the Following VBA Code :

Please Login or Register  to view this content.

And in Cell F6 (where i need the result) I Copy the following Formula :

Please Login or Register  to view this content.

Then when I Run the sheet i got i Error message :

Please i need some expert to revise my mistake and tell me what is wrong....



Thanks !!!

david gonzalez,


The following Function works correctly in your worksheet.

Instructions within the Function:

' If C6 = 28-17-20-32-21-18-21-40-50-60
' If C9 = 18-19-20-21-22-23-24-40-50-60
' The Function/Formula in F6
' =GetMatchV2(C6,C9)
' F6 will display: 20-21-18-21-40-50-60


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Please Login or Register  to view this content.

Follow the instructions to input the Function into F6

As long as the strings in the cells are all the same number of digits these approaches will work, but if the search strings were two digits and the searched string had longer digits, you would get false positives without adjusting these methods to splitting both strings and iterating through both arrays completely. Just a warning.

For instance a search for "20" from this string would give a false positive:


28-17-202-32-21-18-21-40-50-60

david gonzalez,

If you require Function GetMatchV2 to return a Unique result with no duplicates, I will adjust the Function for you.

JBeaucaire,

Nice catch.

Let's see what david gonzalez says.

david gonzalez,

The following Function addresses JBeaucaire's concerns.

Please Login or Register  to view this content.

Hi stanleydgromjr and JBeaucaire

Thank you for your quick response !!

Yes, Mr JBeaucaire is right,

I Post as example and to be short the Match of Cell C6+C9 (with few numbers on each cell)
But in reality there are going to be more than 30 rows combined against other 6 rows to find the match.
and each of the Cell might will have between 12 to 22 Numbers, the Max Number will vary between 36 to 60 (but mostly 36).. (outch...)

Mr stanleydgromjr, i'm noob in excel but i'm trying real hard to learn, and i'm asking my self (please no hard feeling)

Inside the code is the example that i've posted.

'If C6 = 28-17-20-32-21-18-21-40-50-60"
'If C9 = 18-19-20-21-22-23-24-40-50-60"

The question is : the code always will look for those result numbers?


Thank you, and i'm sorry to bother with might (non sense) question (Smile)

Here are threads where I've dealt with this in the past, too.

http://www.excelforum.com/excel-prog...ml#post3045081
http://www.excelforum.com/showthread...=1#post2950219

david gonzalez,


Have you tried my Function GetMatchV3? It does work in your attached workbook.

Add my Function GetMatchV3 to a Module.

In your workbook cell F6, try:
=GetMatchV3(C6,C9)

Try this

Please Login or Register  to view this content.

david gonzalez,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.

david gonzalez

I tried jindon's function.

Please try the slightly modified jindon's code below - it will return a unique sorted result.

Please Login or Register  to view this content.

In cell F6 type in:
=GetMatch(C6,C9)

david gonzalez,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.