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.


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:

📌 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.

sampl dataset : VLOOKUP Partial Match in Table Array in Excel

  • Then, insert this formula into cell H6:
=VLOOKUP("*"&H4&"*",B5:E12,1,FALSE)

🔎 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.

Applying VLOOKUP function with Partial Match in Table Array

  • 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”.

VLOOKUP Partial Match with WildCards in Table Array


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:

📌 Steps:

  • Here, insert the following formula into cell H6:
=VLOOKUP(LEFT($H$4,3)&"*",$B4:$E$12,1,FALSE)
Here, the LEFT function will extract the leftmost 3 characters of cell H4 and the VLOOKUP function will search for the leftmost 3 characters in the first column of the selected table.

Applying VLOOKUP Partial Match for Only First 3 Characters

  • 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.

VLOOKUP Partial Match result for matching only the First 3 Characters


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:

📌 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.

Dataset of VLOOKUp Partial Match for Multiple Values

  • 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:
=C5&D5

Combining column values

Dragging Fill handle

  • Now, you can create a formula using VLOOKUP for a partial match for multiple values. Insert the given formula into cell I7:
=VLOOKUP(I4&"*"&I5&"*",B5:F12,2,0)

🔎 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.

Applying VLOOKUp Partial Match formula for Multiple Values

  • 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.

output value of VLOOKUp Partial Match for Multiple Values

  • 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


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. 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.

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo