How To Fix Vlookup Wildcard Not Working Issue

When I use wildcards in the excel VLOOKUP function to find a wanted cell value, I meet a strange issue. I found the wildcards are not working as I expected. This article will tell you how to fix the VLOOKUP wildcard not working issue.

1. VLOOKUP Wildcards Not Working Example 1.

1.1 Example Description.

1. Below are the example data cells.
   

   	A	B	C
   1	ID	Name	Search Value
   2	1.1	Jerry	1.1*
   3	1.1a	Tom
   4	1.1b	John	Search Result
   5	2.1	Jeff	1.1a
   6	2.1a	Bill
   7	2.1b	Mark

2. There are 3 columns in the above data cells, column A is the ID list, column B is the Name list, and column C contains the Search Value and Search Result cell.
3. The ID list contains values 1.1, 1.1a, 1.1b, 2.1, 2.1a, and 2.1b.
4. The cell below the Search Result cell ( cell C5 ) contains the formula =VLOOKUP(C2, A2:B7,1, FALSE).
5. When you input the search value 1.1* in cell C2, the formula in C5 will show the text 1.1a (cell A3‘s value).
6. But this is not what I want, I want to show the text 1.1 (cell A2‘s value) in cell C5 because the wildcard * means zero or multiple characters.

1.2 How To Fix This Issue?

1. Add the text Helper Field in cell C6, and input the character Z in cell C7, we will use this character as the helper character.
2. Click to select column A and right-click the mouse pointer, then click the Insert menu item in the popup menu list to insert a new column.
3. Now the other columns will move to the next column on the right side automatically.
4. Add the text Helper Column in cell A1.
   

   	A	B	C	D
   1	Helper Column	ID	Name	Search Value
   2		1.1	Jerry
   3		1.1a	Tom
   4		1.1b	John	Search Result
   5		2.1	Jeff
   6		2.1a	Bill	Helper Field
   7		2.1b	Mark	Z

5. Input the formula =CONCAT(B2,$D$7) in cell A2 and press the enter key, then it will show text 1.1Z in cell A2.
6. The excel CONCAT function will add the inputted cell list values to a new value.
7. Click to select cell A2, then click the small square on the bottom right corner of cell A2 and drag the mouse pointer to cell A7.
8. Then it will autofill the formula to the cell range A2:A7, you can get the below table.
   

   	A	B	C	D
   1	Helper Column	ID	Name	Search Value
   2	1.1Z	1.1	Jerry	1.1*
   3	1.1aZ	1.1a	Tom
   4	1.1bZ	1.1b	John	Search Result
   5	2.1Z	2.1	Jeff
   6	2.1aZ	2.1a	Bill	Helper Field
   7	2.1bZ	2.1b	Mark	Z

9. Input the query string 1.1* in cell D2.
10. Input the formula =VLOOKUP(D2, A2:B7,2, FALSE) in cell D5 and press the enter key, then it will show the value 1.1 which we wanted in cell D5.