vlookup with Wildcard(s) in data table Excel 2010

Sparty7

New Member
Joined
Aug 21, 2014
Messages
3
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)
AccountBalance
01A5000
05B2500
16A020
30G102000
30G10OTH7000
30G10CASH450


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

Sheet 2)
AccountBalance
1100041000
1100074000
120008-0091000
120002011500
1301020
1301290
140000101000
140008500
140009500
140010-0014000
140008-0012000
140009-001500
14000001-001500
140020-002250
140029-002200

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


Sheet 3)
AccountAccount
01A11000*
05B12000*
12000*-00*
16A0213010*
30G101400001*
14000*
30G10OTH1400001*-001
14000*-001
14000*
30G10CASH14002*-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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

tmnt43

New Member
Joined
Jun 27, 2014
Messages
18
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

New Member
Joined
Aug 21, 2014
Messages
3
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:

AccountBalanceFormula Result
110004100001A
1100074000
120008100005B
120002011500
130102016A02
1301290
14000010100030G10
140008500
140009500
14000010-001400030G10OTH
140008-0012000
140009-001500
14000001-002500
130102-00125030G10CASH
130111-001200

<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

New Member
Joined
Jun 27, 2014
Messages
18
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

Code:
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
 
Last edited:

Sparty7

New Member
Joined
Aug 21, 2014
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,624
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top