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.
Download Practice Workbook
You can download the practice workbook from here:
3 Examples of Using Excel VLOOKUP Function for Partial Match in Table Array
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 Microsoft 365 version here. But you can use any other versions as of your availability. If anything of 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 functions 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:
- 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:
🔎 Formula Breakdown:
Syntax of VLOOKUP Function: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
- Lookup_value = “*”&H4&”*”: Here, the look_up value will be the H4 cell but using an asterisk(*) at after and before cell reference is one of the Wildcards which is used for commanding partial match.
- Table_array = B5:E12: It is the table array where the Vlookup function will work.
- Col_index_num=1: It will extract the value of 1st column of the row where the search item matches partially.
- Range_lookup = False: False for the Partial Match of the value in the table array.
- As a result, you will get the 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”.
2. VLOOKUP for Partial Match of First 3 Characters Only
Now, you may want to fix the matching character of the input cell. Like, 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:
- 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 value respectively.
3. Look Up Partial Match for Multiple Values
Sometimes, there can be duplicate names in a list but of different IDs. And in that case, you may want to apply the VLOOKUP function with the partial match for multiple values. Follow the steps below for this:
- 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 column “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 respectively 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:
🔎 Formula Breakdown:
Through this formula, you are searching for the row values which match partially with the values inserted in cells I5 and I6. As you have combined the name and ID column in the helper column, you will get the values of rows that contain the characters of I5 and I6 in the helper column.
- 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. And 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 partial match function, you have to notice one more thing. That is, you can’t search with an item which 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
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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.