Vlookup using wildcard

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
224
Hi, I have a credit card bill that i export.

I also have a lookup table.

taxi1234100#6070
taxi99880#6070
starbucks9#6620
tax123488#6070
taxi #555566#6070

<tbody>
</tbody>

I want the table above to do a vlookup in the table below. Look for anything *taxi* and use GL account #6070 . Lookup *starbucks* #6620 .

I will have a look of lookups.

taxi#6070
starbucks#6620
Home Depot#5060

<tbody>
</tbody>


Thanks
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Excel 2010
ABCDEFGH
1
2taxi1234100#6070
3taxi99880#6070taxi#6070
4starbucks9#6620starbucks#6620
5tax123488#6070Home Depot#5060
6taxi #555566#6070

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=VLOOKUP(LEFT($A2,3)&"*",$G$3:$H$5,2,0)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Seems to Work...
 

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
224
Hi, thanks for the reply.

Maybe my example wasn't clear.

Column "C" is the Vlookup formula.

In your formula what is the "3", after the "A2"?

When I paste your example. I get a "0". I would assume that's a false.

I'm trying to find any "TAXI" and having it use GL account #6070 .

Thanks
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
My formula IS in Cell C2

It first looks into A2 and considers ONLY the 1st 3 characters whatever is there ("tax" in this specific case) then adds a "*" to it making the full Lookup Value to be "tax*".
Copy the same C2 formula down to all required cells...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top