Excel VLOOKUP for Partial Match in Table Array (3 Examples)

If you are searching for some tutorial about how to use VLOOKUP for a partial match in any table array of Excel then you have landed in the right place. There are some tricks to using the VLOOKUP for a partial match in any table array of Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.

How to Use Excel VLOOKUP Function for Partial Match in Table Array: 3 Practical Examples

The VLOOKUP function is generally used for searching or finding any cell values within a range or table in Excel. Also, we can get any elements with partial matches using the VLOOKUP function. There are several ways to perform this type of operation.

In this section, I will show you 3 examples using VLOOKUP for partial match in any table array of Excel on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in this article doesn’t work in your version then leave us a comment.

1. Using Wildcards in VLOOKUP Function to Look Up Partially Matched Text

Here, in this example, I will show you how to use the VLOOKUP function with Wildcards for partial matching of any part of the text. In this dataset, here, I have 4 columns and I want to assign a cell where I will insert incomplete names and get the output of the full name which matches the input.  For this, follow the steps below:

📌 Steps:

• First, prepare the dataset and assign cells to insert the lookup value and cells to get the output values.
• Here, I have assigned cell H4 as the lookup cell and cell range H6:H9 to get the output values.

• Then, insert this formula into cell H6:

• As a result, you will get a name that will match partially the input value.

• Then, to get the ID, Joining Date, and Sales value, you have to change the column index number to 2, 3, and 4 respectively in the previous formula.

• As a result, now you can write anything to get the data that will match partially to any cell of the Name column of the dataset.
• For checking, I have inserted “wil” in the search box and got the output for the name “Wilson”.

Read More: How to Use VLOOKUP to Find Partial Text from a Single Cell

2. VLOOKUP for Partial Match of First 3 Characters Only

Now, you may want to fix the matching character of the input cell. Like, for whatever the user may insert in the search box, you will match the first 3 characters with column cells of the dataset. For this, Follow the steps below:

📌 Steps:

• Here, insert the following formula into cell H6:

• Then, similarly change the column index number in the VLOOKUP function as 2, 3, and 4 to get the ID, Date, and Sales values respectively.

Read More: How to Vlookup Partial Match for First 5 Characters in Excel

3. Look Up Partial Match for Multiple Values

Sometimes, there can be duplicate names in a list but of different IDs. In that case, you may want to apply the VLOOKUP function with the partial match for multiple values. Follow the steps below for this:

📌 Steps:

• First, create search boxes to take input of names and IDs where the user can insert the partial part of names and IDs.
• Here, I have assigned the H4 cell and H5 cell to take search input of name and ID respectively.

• Then, you have to create a helper column that will contain the combined text of the two columns where the search items exist. For example: here I want to get items that will partially match the name and ID inserted into the search box. So, I will combine the columns “Full Name” and “ID” in a new column named “Helper Column”.
• So, insert a new column before the column “Full Name”.
• Then, insert the following formula into cell B5:

• Now, drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

• Now, you can create a formula using VLOOKUP for a partial match for multiple values. Insert the given formula into cell I7:

• Then, similarly use the given formula by changing the column index numbers respectively for ID, Joining Date, and Sales.
• Thus, you have created a dynamic Excel file.  you can search for name and id partially without a perfect match and will get the values perfectly for the search items.

• Here, you can see that there are two items in the “Full Name” column that match the search item “Jam” which are “James” and “Jamson”. But, only for the cell containing “James”, the other criteria “6” matches so the output is given for the row which contains the cell “James”.

How to Solve If Excel VLOOKUP for Partial Match in Table Array Does Not Work

Sometimes, you can face the problem when the VLOOKUP for partial match does not work in Excel. There are several reasons for this. here, I am giving 4 reasons why the Excel VLOOKUP for partial match not working with solutions.

1. Wrong Placement of Wildcard Characters

You must have to place the Asterisk (*) / wildcard characters in inverted commas. Also, you have to put an ampersand(&) between the asterisk and the cell reference. The format will be as follows:

VLOOKUP(“*”&lookup_value&”*”,table_array, column_index_num, [range_lookup])

2. False Column Number Reference in Formula

Recheck the column index number and the table array. It is important to know that the look_up value must be in the first column of the selected data range for the table array.

3. Mismatch Between Search and Source Data

While using VLOOKUP for the partial match function, you have to notice one more thing. That is, you can’t search for an item with extra and different characters from the target value.

Here, I wanted to search for “Wilson” but typed “Wilsam”. So, Excel is giving an error. But, if I typed only “Wil” then I would get the results for “Wilson”.

4. Extra Space Inside VLOOKUP Formula

Also, at the time of typing any formula, we need to be very careful of the spacing between symbols and cell numbers.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

In this article, you have found how to use VLOOKUP for partial match in any table array of Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Article

• How to Use Excel VLOOKUP to Find the Closest Match
• How to Use VLOOKUP to Find Approximate Match for Text in Excel

<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel