Comparing two columns with partial values and getting info on match and column number

Question

I used VLOOKUP, MATCH, INDEX and even the Fuzzy add-on. I'm sure one of these or a combination might work, I just don't have the know-how to make it work and get "#N/A" all the time...

Problem: I have 2 columns, with the different total number of items, full of references. One has partially the same numbers from the other, with some changes, like so:

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

But I have thousands of numbers and I want to match each cell from A to the entire range of B and if there's a match, even in cell B6544, I want o know and get something (in column C) like "True B6544".

A (small) example of what I'm dealing with (and not the exact references, those are in-house sensible data):

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

As you can see, J1 is a partial match to B1, so H1 returns "True, B1".

As said above, I tried MATCH, VLOOKUP, INDEX and the Fuzzy add-on. I understand that I have to so something like:

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

But nothing seems to work... any help will be appreciated!

Answer 1

Try this small User Defined Function:

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

As the illustration shows, in K1 enter:

=partialmatch($B$1:$B$10,J1)

and copy downward.

The routine strips the leading digit from the sub-string and tries to find it within the column.

Answer 2

Since Excel doesn't support regular expressions, I don't think there is a formula that doesn't need an auxilliary column. In my example,

• A contains the numbers with a "-"
• B contains the numbers that are matched
• C contains the values of B without the first digit
• D is "FALSE" if the value of A doesn't match any in column B, or "TRUE, " + reference to the matching cell.

The formula in C1 is:

=RIGHT(B1,LEN(B1)-1)

This removes the first digit of B1.

The formula in D1 is:

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

Note that, although this should produce the desired output, I would recommend splitting the TRUE/FALSE and the cell reference in two columns. Therefore, I will only explain the ADDRESS part, which will give you the cell reference if a match is found, or an error otherwise.

VLOOKUP is not useful here, because it will return a value in the same row as the matched value. MATCH, on the other hand, returns the row of the matched value.

• MID(A1,2,SEARCH("-",A1)-2) returns the substring of A1, beginning with the second character, up to and excluding the first occurrence of "-". This is the value we are looking for (lookup_value).
• MATCH(lookup_value, C$1:C$10, 0) will return the row of the first occurrence of lookup_value. Since we cannot apply a function to the range and Excel doesn't support regular expressions, we need the auxiliary column C. The last parameter (0) is needed because the values are not sorted. Note that the returned row value is relative to the specified range, so if your range doesn't start at row 1, you will have to account for that (e.g. by adding ROW([first cell])-1 to the result of MATCH).
• ADDRESS(matched_row, COLUMN(B1)) produces the cell reference. You can use the absolute number of the row instead of COLUMN(B1) if you like, but that wouldn't be as human readable.

You have to decide for yourself which references should be absolute or relative.

Answer 3

I'm attempting to partially answer your question as we do not have the complete picture yet.

I have added two helper columns to process the data and compare them - which will give you a better understanding.

The C-helper column strips the 0 in the beginning and the trailing - number.
The I- helper column removes the leading digit (or the first digit) of the J col.
Then I have done a vlookup of data in I-helper against C - helper and finding out if the data is found or not found based on vlookup() result.

The formula I have in

• C - helper : ==MID(B4,2,LEN(B4)-3)
• I - helper : =MID(D4,2,LEN(D4)-1)
• vlookup col : =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

The next part of the problem is to locate where this entry was found. If the entry is found - its address is returned else the string Not_Available is returned. The sample data now looks like this

A   B           C - helper  J           I - helper  vlookup col H       I   X-helper
1   025983553-1 25983553    225983553   25983553    found       True,   B1  $C$4
2   025973223-1 25973223    222222345   22222345    not_found   FALSE       Not_Available
3   025965463-2 25965463    233444667   33444667    not_found   FALSE       Not_Available
4   025911122-4 25911122    211198989   11198989    not_found   FALSE       Not_Available
5   025998764-1 25998764    212989238   12989238    not_found   FALSE       Not_Available
6   025925925-3 25925925    224397501   24397501    not_found   FALSE       Not_Available
7   025900000-2 25900000    225973223   25973223    found   True,       B2  $C$5
8   025999999-5 25999999    223334445   23334445    not_found   FALSE       Not_Available
9   025965453-6 25965453    211100110   11100110    not_found   FALSE       Not_Available
10  025943536-2 25943536    225911122   25911122    found   True,       B4  $C$7

Edit

The formula I have in

• X - helper : =IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")