# Vlookup with wildcard

#### John Davis

##### Well-known Member
Hello All:

It's been awhile since I used VLOOKUP with a wildcard. However, here is what I have.

In Column A Sheet1 I have a list of numbers. I'm trying to data from Sheet2 Column B. Sheet 2 Column A has the list of numbers.

=VLOOKUP("*"&A2&"*",Sheet2!\$A\$2:\$B\$6,2,FALSE) this returns #N/A

=VLOOKUP(A2,Sheet2!\$A\$2:\$B\$6,2,FALSE) this returns the correct result.

However, I need the wildcard since Sheet 2 Column A does not always have a direct match.

Can someone provide an explanation/guidance on what may be happening?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hello All:

It's been awhile since I used VLOOKUP with a wildcard. However, here is what I have.

In Column A Sheet1 I have a list of numbers. I'm trying to data from Sheet2 Column B. Sheet 2 Column A has the list of numbers.

=VLOOKUP("*"&A2&"*",Sheet2!\$A\$2:\$B\$6,2,FALSE) this returns #N/A

=VLOOKUP(A2,Sheet2!\$A\$2:\$B\$6,2,FALSE) this returns the correct result.

However, I need the wildcard since Sheet 2 Column A does not always have a direct match.

Can someone provide an explanation/guidance on what may be happening?
Wildcards don't work on numbers.

What's in A2 and what are you trying to match it to?

Wildcards don't work on numbers.

What's in A2 and what are you trying to match it to?

Thanks for your reply. It was a number, and then I changed it too text, as well as Column A in Sheet2, but I still get the same result.

Sheet1

12345 #N/A AAA
6789
101112

Sheet2

12345 AAA
6789, 1879 BBB
101112 CCC
2468, 3571 DDD
46810, 111 EEE

Last edited:
Thanks for your reply. It was a number, and then I changed it too text, as well as Column A in Sheet2, but I still get the same result.

Sheet1

12345 #N/A AAA
6789
101112

Sheet2

12345 AAA
6789, 1879 BBB
101112 CCC
2468, 3571 DDD
46810, 111 EEE
How did you change it to text? Just changing the format doesn't change the data type.

Try this...

Book1
AB
212345AAA
36789, 1879BBB
4101112CCC
52468, 3571DDD
646810, 111EEE
Sheet2

Book1
AB
212345AAA
36789BBB
4101112CCC
Sheet1

This array formula** entered in B2 and copied down:

=INDEX(Sheet2!B\$2:B\$6,MATCH("*"&A2&"*",Sheet2!A\$2:A\$6&"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

How did you change it to text? Just changing the format doesn't change the data type.

Try this...

Book1
*AB
212345AAA
36789, 1879BBB
4101112CCC
52468, 3571DDD
646810, 111EEE
Sheet2

Book1
*AB
212345AAA
36789BBB
4101112CCC
Sheet1

This array formula** entered in B2 and copied down:

=INDEX(Sheet2!B\$2:B\$6,MATCH("*"&A2&"*",Sheet2!A\$2:A\$6&"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

That's perfect. Thanks alot for your help.

That's perfect. Thanks alot for your help.
You're welcome. Thanks for the feedback!

Replies
4
Views
93
Replies
3
Views
217
Replies
16
Views
261
Replies
1
Views
74
Replies
1
Views
374

1,203,600
Messages
6,056,205
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back