How to make vlookup partial match digits from left to right?

[Fractalis]

Hi all,

I'm trying to get a formula to do exact and partial matches.

I use INDEX(...MATCH(...)) to look for exact matches and if fails, then I try to do partial matches using VLOOKUP() but is not showing expected output.

The partial match should show the "best match", this is select the number that has more numbers in common in same order. In other words, select the number that
shares the longest common prefix. The minimun prefix length to match would be 4 digits, is only match 3 digits or none show "NOT FOUND".

I hope make sense, thanks for any help.

Examples:

For number 50660000 in D3, the more similar numbers in column B are 5067000,5063005,506600002 and within these 3, the more similar is 506600002 because shares common
prefix of length 8.
50660000
506600002

For number 3520913 in D6, the more similar numbers in column B are 352021,352621,352091000 and within these 3, the more similar is 352091000 because shares common
prefix of length 6.
3520913
352091000

For number 13603399617 in D9, the more similar numbers in column B are 1360339,136033 and within these 2, the more similar is 1360339 because shares common prefix of length 7.
13603399617
1360339

For number 32486 in D7, the more similar number in column B is 32475 but only has the first 3 numbers in common, so show "NOT FOUND"
32486
32475

=IFERROR(IFERROR(
        INDEX($A$2:$A$29,MATCH(D2,$B$2:$B29,0)),  --> This do exact match
        VLOOKUP(D2&"*",$A$1:$B$29,2,TRUE)         --> If exact match fails, try partial match
        ),
"NOT FOUND")

Search numbers.xlsx
	A	B	C	D	E	F
1	CODE	NUMBER		NUMBER	CURRENT OUTPUT	EXPECTED OUTPUT
2	XZS90	5067000		50660000	NOT FOUND	XZS76
3	XZS78	5063005		423780000	NOT FOUND	XZS13
4	XZS76	506600002		39370	XZS81	XZS81
5	XZS29	32475		39335	NOT FOUND	NOT FOUND
6	XZS39	4206080055		3520913	NOT FOUND	XZS71
7	XZS64	420602		32486	NOT FOUND	NOT FOUND
8	XZS35	42379		1807631	NOT FOUND	NOT FOUND
9	XZS13	4237800		13603399617	NOT FOUND	XZS99
10	XZS96	4237807672		5257000	XZS05	XZS05
11	XZS16	42397030
12	XZS05	5257000
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
18	XZS27	1809859
19	XZS99	1360339
20	XZS41	136033
21	XZS98	1802692
22	XZS60	180825
23	XZS93	393533000
24	XZS58	3932059
25	XZS31	393205800448
26	XZS89	352021
27	XZS46	352621
28	XZS71	352091000
29	XZS81	39370
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(IFERROR( INDEX($A$2:$A$29,MATCH(D2,$B$2:$B29,0)), VLOOKUP(D2&"*",$A$1:$B$29,2,TRUE) ), "NOT FOUND")

 

[lrobbo314]

This VBA custom function seems pretty close. But I'm not sure about your logic for the "Not Found" ones. Especially with, '1807631'. Because it matches more than the first 3 characters. I added threshold as an argument in the function so you can adjust it.

Prcatice.xlsx
	D	E	F
1	NUMBER	EXPECTED OUTPUT	VBA
2	50660000	XZS76	XZS76
3	423780000	XZS13	XZS13
4	39370	XZS81	XZS81
5	39335	NOT FOUND	Not Found
6	3520913	XZS71	XZS71
7	32486	NOT FOUND	Not Found
8	1807631	NOT FOUND	XZS56
9	13603399617	XZS99	XZS99
10	5257000	XZS05	XZS05
Sheet4

Cell Formulas
Range	Formula
F2:F10	F2	=RLOOKUP(D2,$B$2:$B$29,$A$2:$A$29,3)

Function RLOOKUP(xVal As String, r As Range, lk As Range, threshold As Integer)
Dim AR() As Variant:    AR = r.Value2
Dim HS As Integer:      HS = 0
Dim TMP As Integer:     TMP = 0
Dim IDX As Integer:     IDX = 0

Dim b() As Byte:        b = xVal
Dim t() As Byte:

For i = 1 To UBound(AR)
    t = CStr(AR(i, 1))
    TMP = 0
    If UBound(b) < UBound(t) Then
        For j = 0 To UBound(b) Step 2
            If b(j) = t(j) Then TMP = TMP + 1
        Next j
    Else
        For j = 0 To UBound(t) Step 2
            If b(j) = t(j) Then TMP = TMP + 1
        Next j
    End If
    If TMP > HS Then
        HS = TMP
        IDX = i
    End If
Next i
If HS <= threshold Then
    RLOOKUP = "Not Found"
Else
    RLOOKUP = lk.Cells(IDX).Value
End If
End Function

 

[DanteAmor]

Edit.

 

[Anonymous1378]

I'm almost there, but I have a discrepancy for 1807631, since there are two values which start with 1807 in column B.

Book8
	A	B	C	D	E	F
1	CODE	NUMBER		NUMBER	CURRENT OUTPUT	EXPECTED OUTPUT
2	XZS90	5067000		50660000	XZS76	XZS76
3	XZS78	5063005		423780000	XZS13	XZS13
4	XZS76	506600002		39370	XZS81	XZS81
5	XZS29	32475		39335	NOT FOUND	NOT FOUND
6	XZS39	4206080055		3520913	XZS71	XZS71
7	XZS64	420602		32486	NOT FOUND	NOT FOUND
8	XZS35	42379		1807631	XZS56	NOT FOUND
9	XZS13	4237800		13603399617	XZS99	XZS99
10	XZS96	4237807672		5257000	XZS05	XZS05
11	XZS16	42397030
12	XZS05	5257000
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
18	XZS27	1809859
19	XZS99	1360339
20	XZS41	136033
21	XZS98	1802692
22	XZS60	180825
23	XZS93	393533000
24	XZS58	3932059
25	XZS31	3.93206E+11
26	XZS89	352021
27	XZS46	352621
28	XZS71	352091000
29	XZS81	39370
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR( LOOKUP(FALSE,ISERROR(INDEX($A$2:$A$29,MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0))), INDEX($A$2:$A$29,MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0))), "NOT FOUND")

 

[Anonymous1378]

Oops, slight amendment to be made, there seems to be an error when entered as an array formula, which is needed for Excel 2019. Made it slightly more compact as well. Previous discrepancy still stands.

Book9
	A	B	C	D	E	F
1	CODE	NUMBER		NUMBER	CURRENT OUTPUT	EXPECTED OUTPUT
2	XZS90	5067000		50660000	XZS76	XZS76
3	XZS78	5063005		423780000	XZS13	XZS13
4	XZS76	506600002		39370	XZS81	XZS81
5	XZS29	32475		39335	NOT FOUND	NOT FOUND
6	XZS39	4206080055		3520913	XZS71	XZS71
7	XZS64	420602		32486	NOT FOUND	NOT FOUND
8	XZS35	42379		1807631	XZS56	NOT FOUND
9	XZS13	4237800		13603399617	XZS99	XZS99
10	XZS96	4237807672		5257000	XZS05	XZS05
11	XZS16	42397030
12	XZS05	5257000
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
18	XZS27	1809859
19	XZS99	1360339
20	XZS41	136033
21	XZS98	1802692
22	XZS60	180825
23	XZS93	393533000
24	XZS58	3932059
25	XZS31	3.93206E+11
26	XZS89	352021
27	XZS46	352621
28	XZS71	352091000
29	XZS81	39370
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR( INDEX($A$2:$A$29, LOOKUP(2,SIGN(MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0)), MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0))), "NOT FOUND")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fractalis]

This VBA custom function seems pretty close. But I'm not sure about your logic for the "Not Found" ones. Especially with, '1807631'. Because it matches more than the first 3 characters. I added threshold as an argument in the function so you can adjust it.

Prcatice.xlsx
	D	E	F
1	NUMBER	EXPECTED OUTPUT	VBA
2	50660000	XZS76	XZS76
3	423780000	XZS13	XZS13
4	39370	XZS81	XZS81
5	39335	NOT FOUND	Not Found
6	3520913	XZS71	XZS71
7	32486	NOT FOUND	Not Found
8	1807631	NOT FOUND	XZS56
9	13603399617	XZS99	XZS99
10	5257000	XZS05	XZS05
Sheet4

Cell Formulas
Range	Formula
F2:F10	F2	=RLOOKUP(D2,$B$2:$B$29,$A$2:$A$29,3)

Function RLOOKUP(xVal As String, r As Range, lk As Range, threshold As Integer)
Dim AR() As Variant:    AR = r.Value2
Dim HS As Integer:      HS = 0
Dim TMP As Integer:     TMP = 0
Dim IDX As Integer:     IDX = 0

Dim b() As Byte:        b = xVal
Dim t() As Byte:

For i = 1 To UBound(AR)
    t = CStr(AR(i, 1))
    TMP = 0
    If UBound(b) < UBound(t) Then
        For j = 0 To UBound(b) Step 2
            If b(j) = t(j) Then TMP = TMP + 1
        Next j
    Else
        For j = 0 To UBound(t) Step 2
            If b(j) = t(j) Then TMP = TMP + 1
        Next j
    End If
    If TMP > HS Then
        HS = TMP
        IDX = i
    End If
Next i
If HS <= threshold Then
    RLOOKUP = "Not Found"
Else
    RLOOKUP = lk.Cells(IDX).Value
End If
End Function

Thanks so much, it seems to work. I think your output is correct, including the output for 1807631, that your code is taking the first appearence.

 

[Fractalis]

Oops, slight amendment to be made, there seems to be an error when entered as an array formula, which is needed for Excel 2019. Made it slightly more compact as well. Previous discrepancy still stands.

Book9
	A	B	C	D	E	F
1	CODE	NUMBER		NUMBER	CURRENT OUTPUT	EXPECTED OUTPUT
2	XZS90	5067000		50660000	XZS76	XZS76
3	XZS78	5063005		423780000	XZS13	XZS13
4	XZS76	506600002		39370	XZS81	XZS81
5	XZS29	32475		39335	NOT FOUND	NOT FOUND
6	XZS39	4206080055		3520913	XZS71	XZS71
7	XZS64	420602		32486	NOT FOUND	NOT FOUND
8	XZS35	42379		1807631	XZS56	NOT FOUND
9	XZS13	4237800		13603399617	XZS99	XZS99
10	XZS96	4237807672		5257000	XZS05	XZS05
11	XZS16	42397030
12	XZS05	5257000
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
18	XZS27	1809859
19	XZS99	1360339
20	XZS41	136033
21	XZS98	1802692
22	XZS60	180825
23	XZS93	393533000
24	XZS58	3932059
25	XZS31	3.93206E+11
26	XZS89	352021
27	XZS46	352621
28	XZS71	352091000
29	XZS81	39370
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR( INDEX($A$2:$A$29, LOOKUP(2,SIGN(MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0)), MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0))), "NOT FOUND")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks so much. It works pretty fine even without entering as array formula. It seems to be a complex formula to me . Nice!

 

[Fractalis]

Oops, slight amendment to be made, there seems to be an error when entered as an array formula, which is needed for Excel 2019. Made it slightly more compact as well. Previous discrepancy still stands.

Book9
	A	B	C	D	E	F
1	CODE	NUMBER		NUMBER	CURRENT OUTPUT	EXPECTED OUTPUT
2	XZS90	5067000		50660000	XZS76	XZS76
3	XZS78	5063005		423780000	XZS13	XZS13
4	XZS76	506600002		39370	XZS81	XZS81
5	XZS29	32475		39335	NOT FOUND	NOT FOUND
6	XZS39	4206080055		3520913	XZS71	XZS71
7	XZS64	420602		32486	NOT FOUND	NOT FOUND
8	XZS35	42379		1807631	XZS56	NOT FOUND
9	XZS13	4237800		13603399617	XZS99	XZS99
10	XZS96	4237807672		5257000	XZS05	XZS05
11	XZS16	42397030
12	XZS05	5257000
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
18	XZS27	1809859
19	XZS99	1360339
20	XZS41	136033
21	XZS98	1802692
22	XZS60	180825
23	XZS93	393533000
24	XZS58	3932059
25	XZS31	3.93206E+11
26	XZS89	352021
27	XZS46	352621
28	XZS71	352091000
29	XZS81	39370
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR( INDEX($A$2:$A$29, LOOKUP(2,SIGN(MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0)), MATCH(MID(D2,1,ROW(INDIRECT("4:"&LEN(D2))))&"*",$B$2:$B$29&"",0))), "NOT FOUND")
Press CTRL+SHIFT+ENTER to enter array formulas.

I'd like to ask if I have 2 databases to search from (A1:Bn and D1:Dn), should I repeat twice your formula with both input ranges? for example. I'd like to search first in DB1 and if not found there, search in DB2. Is possible to use your formula with both ranges but searching first in A1:An and if not found search in D1:Dn but entering a single formula? I hope make sense

CODE_DB1	NUMBER		CODE_DB2	NUMBER		NUMBER TO SEARCH	OUTPUT
XZS90	5067000		XZS67	467275799		50660000
XZS78	5063005		XZS68	26879		423780000
XZS76	506600002		XZS69	26876		39370
XZS29	32475		XZS70	24825		39335
XZS39	4206080055		XZS71	24826		3520913
XZS64	420602		XZS72	9639310		32486
XZS35	42379		XZS73	9639311		1807631
XZS13	4237800		XZS74	96393111		13603399617
XZS96	4237807672		XZS75	1246253		5257000
XZS16	42397030		XZS76	1268789
XZS05	5257000		XZS77	1345990
XZS47	421903
XZS15	421905
XZS40	4206020899
XZS56	18072141180
XZS03	18072144650

 

[Anonymous1378]

The easiest way is to probably nest the second lookup in another IFERROR().

I'm surprised it works without an array formula. Are you still using Excel 2019? There are more compact options (i.e. XLOOKUP, XMATCH) if you've upgraded to excel 2021 and later

Book1
	A	B	C	D	E	F	G	H
1	CODE_DB1	NUMBER		CODE_DB2	NUMBER		NUMBER TO SEARCH	OUTPUT
2	XZS90	5067000		XZS67	467275799		50660000	XZS76
3	XZS78	5063005		XZS68	26879		423780000	XZS13
4	XZS76	506600002		XZS69	26876		39370	NOT FOUND
5	XZS29	32475		XZS70	24825		39335	NOT FOUND
6	XZS39	4206080055		XZS71	24826		3520913	NOT FOUND
7	XZS64	420602		XZS72	9639310		32486	NOT FOUND
8	XZS35	42379		XZS73	9639311		1807631	XZS56
9	XZS13	4237800		XZS74	96393111		13603399617	NOT FOUND
10	XZS96	4237807672		XZS75	1246253		5257000	XZS05
11	XZS16	42397030		XZS76	1268789
12	XZS05	5257000		XZS77	1345990
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=IFERROR( IFERROR( INDEX($A$2:$A$17, LOOKUP(2,SIGN(MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$B$2:$B$17&"",0)), MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$B$2:$B$17&"",0))), INDEX($D$2:$D$12, LOOKUP(2,SIGN(MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$E$2:$E$12&"",0)), MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$E$2:$E$12&"",0)))), "NOT FOUND")

 

[Fractalis]

The easiest way is to probably nest the second lookup in another IFERROR().

I'm surprised it works without an array formula. Are you still using Excel 2019? There are more compact options (i.e. XLOOKUP, XMATCH) if you've upgraded to excel 2021 and later

Book1
	A	B	C	D	E	F	G	H
1	CODE_DB1	NUMBER		CODE_DB2	NUMBER		NUMBER TO SEARCH	OUTPUT
2	XZS90	5067000		XZS67	467275799		50660000	XZS76
3	XZS78	5063005		XZS68	26879		423780000	XZS13
4	XZS76	506600002		XZS69	26876		39370	NOT FOUND
5	XZS29	32475		XZS70	24825		39335	NOT FOUND
6	XZS39	4206080055		XZS71	24826		3520913	NOT FOUND
7	XZS64	420602		XZS72	9639310		32486	NOT FOUND
8	XZS35	42379		XZS73	9639311		1807631	XZS56
9	XZS13	4237800		XZS74	96393111		13603399617	NOT FOUND
10	XZS96	4237807672		XZS75	1246253		5257000	XZS05
11	XZS16	42397030		XZS76	1268789
12	XZS05	5257000		XZS77	1345990
13	XZS47	421903
14	XZS15	421905
15	XZS40	4206020899
16	XZS56	18072141180
17	XZS03	18072144650
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=IFERROR( IFERROR( INDEX($A$2:$A$17, LOOKUP(2,SIGN(MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$B$2:$B$17&"",0)), MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$B$2:$B$17&"",0))), INDEX($D$2:$D$12, LOOKUP(2,SIGN(MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$E$2:$E$12&"",0)), MATCH(MID(G2,1,ROW(INDIRECT("4:"&LEN(G2))))&"*",$E$2:$E$12&"",0)))), "NOT FOUND")

Excellent. It works, the only issue is that now appears the meassage "Calculating (8 threads).... X%" in the right low corner of excel sheet and gets stuck for several seconds. I think is because the numbers to search in Column G are about 2500, Data base1 it has more than 4k rows and DB2 has almost 4k rows.

I don't have the option to change to Excel 2021 since is a corporative MS office.