1

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!

  • Are the numbers guaranteed to have the same number of digits/characters? Also, please give us an example of what you tried that didn't work. – danzel Mar 29 '18 at 9:55
  • Hi Danzel. No, the numbers aren't guaranteed to have the same number of digits... :/ I'm afraid I don't remember exactly everything I tried, except the names of the functions I used and the fact that by the end I was locking the range of J and trying to force B to be compared to it... and using excel's "nature" of copying and adding to the cell value to change the value of B over time (when I dragged B1 below it would be B2 but still go through J1 to J10...) Thank you for your time! – Joaquim Santos Mar 29 '18 at 10:19
  • 1
    Please clarify the format of J and which part of B it has to match. If, say, B is "1234-5", would "234" match? What about "23" or "34"? Are the numbers in J unique? In your examples, B always starts with a "0", then there is a number that can occur in J (except the "2" that is always in front of J), and "-" and a single digit appended. Please update your examples to include differrences that can occur, e.g. different number of digits, B not beginning with "0" (or maybe more than one digit?). You should also give examples of values that should not match. – danzel Mar 29 '18 at 10:34
  • Will try to do it when I get the file again (I'm not in the workplace right now...). But to answer your questions: J is an integer with X digits. It is composed of the entirety of B, except for the 0 (which all B entries have at the start) and whatever is after (and including) the "-". Numbers in J are unique and they all start with a digit (2 or 8...) and then use the numbers of B as stated above. Not sure about your last question, as I already placed some examples that don't match i.e. they don't exist in column B. Thanks again for your time and interest! – Joaquim Santos Mar 29 '18 at 10:51
  • I have added an answer, based on more information you provide, I can guide you to the real solution you desire – Prasanna Mar 29 '18 at 12:15
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

enter image description here

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.

  • Thank you Gary's Student! I'll try your answer as soon as possible and will give feedback! – Joaquim Santos Mar 29 '18 at 13:00
1

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.

0

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")
  • Thank you for your time Prasanna. I'll give feedback as soon as possible. How do you get to H column? Something like - pseudo-code - if (found): "True", B+cell number... – Joaquim Santos Mar 29 '18 at 12:57
  • @Prasanna the VLOOKUP is redundant and causes each row to be matched twice. One MATCH is all you need. – danzel Mar 29 '18 at 13:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.