vlookup with Wildcard(s) in data table Excel 2010

[Sparty7]

Hi All,

First time, long time here and looking for a little specialized help. I am looking to do a cross system data validation template and each have unique and very different account codes that I need to cross reference. The biggest issue I am running into is that there is at least one wildcard, sometimes two, in the table that my vlookup needs to reference and therefore is beyond my abilities to figure out. Currently I have my workbook set up with 3 sheets - 1st is system data "A", 2nd is system data "B" and the third is the mapping sequence:

Sheet 1)

Account	Balance
01A	5000
05B	2500
16A02	0
30G10	2000
30G10OTH	7000
30G10CASH	450

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Sheet 2)

Account	Balance
110004	1000
110007	4000
120008-009	1000
12000201	1500
130102	0
130129	0
14000010	1000
140008	500
140009	500
140010-001	4000
140008-001	2000
140009-001	500
14000001-001	500
140020-002	250
140029-002	200

<colgroup><col><col></colgroup><tbody>
</tbody>

Sheet 3)

Account	Account
01A	11000*
05B	12000*
	12000*-00*
16A02	13010*
30G10	1400001*
	14000*
30G10OTH	1400001*-001
	14000*-001
	14000*
30G10CASH	14002*-002

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Any insight would be greatly appreciated as I am stumped! Thanks in advance!

 

[tmnt43]

I'm fairly certain this can't be done with 1 formula, though people tend to amaze with what they can do with formulas. The wildcards in multiple positions is really the dealbreaker here, as far as I'm aware.

A solution would be possible with VBA - not sure if that's an option for you though. What's the desired output?

 

[Sparty7]

I would have to agree that this probably cannot be done with just 1 formula though I have come close with =iferrors( and nested vlookups, but that 2nd wildcard has been my Kryptonite.

Just an update I actually have the columns flipped in my mapping sheet #3 so the column with the wildcards is in column 1 and the alpha numeric accounts are in column 2.

I am not opposed to using VBA, but am very rusty. Basically what I need to do is use the mapping sequence to pull the accounts from sheet 1 to match what I have in sheet 2. Should look something like so for sheet 2:

Account	Balance	Formula Result
110004	1000	01A
110007	4000
120008	1000	05B
12000201	1500
130102	0	16A02
130129	0
14000010	1000	30G10
140008	500
140009	500
14000010-001	4000	30G10OTH
140008-001	2000
140009-001	500
14000001-002	500
130102-001	250	30G10CASH
130111-001	200

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I then used a pivot table to create a way to use a vlookup and validate that the sheet 1 balances = sheet 2 balances.

 

[tmnt43]

Well, wrote some code that sort of works. I realized at the end that your wildcards are actually pretty generic. To a computer, 1400001* and 1400001*-001 could be the same value.

Try this code in a copy of your workbook, but basically, without a bunch of custom handling, this may not be that simple

Sub tester1()


Dim accountWs As Worksheet, matchWs As Worksheet
Dim accountWsRc As Double, matchWsRc As Double
Dim i As Double, j As Double
Dim accountId As String


Set accountWs = Sheets("Sheet3")
Set matchWs = Sheets("Sheet2")


'get last row of accountWs and matchWs
accountWsRc = accountWs.Cells(matchWs.Rows.Count, 1).End(xlUp).Row
matchWsRc = matchWs.Cells(matchWs.Rows.Count, 1).End(xlUp).Row


startloop = 2 'assuming data starts in row 2


For i = startloop To accountWsRc


    accountId = accountWs.Cells(i, 2).Value
    replaceId = accountWs.Cells(i, 1).Value
    
    For j = startloop To matchWsRc
        
        If Right(accountId, 1) = "*" And matchWs.Cells(j, 3).Value = "" Then
            If matchWs.Cells(j, 1).Value Like accountId Or matchWs.Cells(j, 1).Value = accountId Then
                matchWs.Cells(j, 3).Value = replaceId
            End If
        Else
            If matchWs.Cells(j, 1).Value Like accountId Or matchWs.Cells(j, 1).Value = accountId Then
                matchWs.Cells(j, 3).Value = replaceId
            End If
        End If
        
    Next j
    
Next i


End Sub

 

[Sparty7]

Thanks for the help. After playing around with it a but I think I was able to tailor it to fit my needs. I appreciate the help.