Vlookup with wildcard

[John Davis]

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?

 

[T. Valko]

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?

 

[John Davis]

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

 

[T. Valko]

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
	A	B
2	12345	AAA
3	6789, 1879	BBB
4	101112	CCC
5	2468, 3571	DDD
6	46810, 111	EEE
Sheet2

Book1
	A	B
2	12345	AAA
3	6789	BBB
4	101112	CCC
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.

 

[John Davis]

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

Try this...

Book1
*	A	B
2	12345	AAA
3	6789, 1879	BBB
4	101112	CCC
5	2468, 3571	DDD
6	46810, 111	EEE
Sheet2

Book1
*	A	B
2	12345	AAA
3	6789	BBB
4	101112	CCC
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.

 

[T. Valko]

That's perfect. Thanks alot for your help.

You're welcome. Thanks for the feedback!