In Google Sheets, with the help of an **asterisk wildcard (*)** in **the VLOOKUP function**, you can look up values that have a partial match with certain text instead of the exact match of the values. In this article, we will learn about **VLOOKUP** partial match in Google Sheets.

## A Sample of Practice Spreadsheet

You can download spreadsheets from here and practice.

## 3 Ideal Examples to VLOOKUP for Partial Match in Google Sheets

The asterisk wildcard represents a number of characters. For example, the string ‘**Wh**’ can represent **What**, **When**, **Who,** **Where,** etc. So, if you want to look up any value that may not exactly match your dataset, you can put this wildcard in the **VLOOKUP function** and get the desired result.

You can use this **asterisk wildcard (*) **to look up a partial match from the beginning, end middle of the test. Here, we develop a dataset containing two columns namely: **Employee Name**, and **Department**. We also add **Lookup Value** and **Result **in rows to apply the function in the dataset.

In the **VLOOKUP **function, the use of wildcards depends on the partial text type.

### 1. Match Partial Text

The most common type of looking up partial values is text values. These match conditions can be found in three locations of a text: **beginning**, **middle**, or **end**.

#### I. Beginning of Text

To look up the value that has a partial match with the beginning of the exact text in the dataset,

- First insert the partial value in the
**Lookup Value**cell. Here we add ‘**Oli**’ as a partial match.

- Then apply the
**VLOOKUP**function in the**Result**cell**F5**.

- Now, input the partial match value as
**search key**.

- After that add
**‘&’**and an**asterisk (*).**The*****wildcard represents any value after the data in cell**F4**. - Whereas the
**&**symbol joins the cell reference and the wildcard into one statement.

- Then insert the range in the function. Here, we add the entire dataset as range
**B:C**in the function.

- Add the
**index**value**1**. Here,**1**represents the column number of the data table.

- Set the
**is_sorted**parameter to**FALSE**to extract the exact value.

- Finally, press
**ENTER**to get the desired value in the selected cell,

`=VLOOKUP(F4&”*”,B:C,1,FALSE)`

- You can apply the same function to the second column of the dataset. In that case, you just change the column number in the function. The rest of the function will be the same.

`=VLOOKUP(F4&”*”,B:C,2,FALSE)`

**Read More: How to Use Wildcard in Google Sheets (3 Practical Examples)**

#### II. End of Text

If the partial value represents the end of the text, then there is a slight difference in function. In that case,

- First of all, input the partial value that you have in the
**Lookup Value**cell.

- Then insert the
**VLOOKUP**function in cell**F6**. In the function**first,**add the**“*”**character.

- After the wildcard character, use the ‘
**&**’ symbol as a connector and add the**F4**cell as the search key of the function.

- After that insert
**range**,**index**, and**is_sorted**as like we show in the previous one.

- In the end, press
**ENTER**to input the outcome in your selected cell.

`=VLOOKUP(“*”&F4,B:C,1,FALSE)`

#### III. Middle of Text

If the partial match value contains the middle portion of the exact text, then the **VLOOKUP** function will be a little bit complex. To look up the middle of the text,

- At first, add the partial match in the
**Lookup Value**cell. Here, we add ‘**illi**’ as our lookup value. It contains the middle portion of an employee’s name.

- Apply the
**VLOOKUP**function then adds “*****” at the beginning. As the beginning and end text is unknown, so insert the “*****” wildcard character*before and after*the**search key.**Connect the whole statement with the**‘&’**symbol**.**Here, we add the**F4**cell as the search key.

- After that input the rest of the function as per the previous instruction.

- Finally, apply the
**Final formula**in cell**F5**and find the desired result. Here we find ‘**William**‘ as final outcome.

`=VLOOKUP(“*”&F4&"*",B:C,1,FALSE)`

**Similar Reading: **

**Create Hyperlink to VLOOKUP Cell in Multiple Rows in Google Sheets****How to Use IFERROR with VLOOKUP Function in Google Sheets****Alternative to Use VLOOKUP Function in Google Sheets****How to Use VLOOKUP for Conditional Formatting in Google Sheets****VLOOKUP Multiple Columns in Google Sheets (3 Ways)**

### 2. Match Partial Numbers

You can also apply the partial match value to look up Numeric data as well as dates from your dataset. Generally, the **VLOOKUP search key** does not support numeric value, but by modifying the formula we can extract both the number and date from the dataset.

For looking up numbers from the dataset through partial numbers with the **VLOOKUP **function, we have to apply **the ARRAYFORMULA function**. To do so,

- First, develop a dataset containing number values. Here, our dataset represents
**Employee Name**and**Age**columns. Besides, we input**2**as the**Lookup Value**.

- Then in cell
**F5**apply the formula.

`=ARRAYFORMULA(VLOOKUP(“*2”,{TO_TEXT(C5:C10),B5:B10},1,FALSE))`

**Formula Breakdown**

**TO_TEXT(C5:C10):**Here,**C5:C10**is the age range which represents in numeric form.**The TO_TEXT function**converts the numeric range to the text range.**B5:B10:**This is the text range that represents the**Employee Name**.**“*2”:**It represents the search key for the**VLOOKUP**function.**2:**This is the column number and**FALSE**is set as**is_sorted**to extract the exact value.**VLOOKUP(….):**Here, the function lookup for a numeric value that ends with**2**where the**TO_TEXT**function helps to convert the numeric value to the text.**ARRAYFORMULA(…..):**Here, the**ARRAYFORMULA**function is used to support the non-array**TO_TEXT**function.

- Press
**ENTER**to apply the formula in the**F5**cell and find the full numeric value that exist in the main dataset.

### 3. Match Partial Dates

You can use the same formula to look up dates in the dataset. Here, we use a dataset representing **Employee Name** and **Date of Birth. **We look up values ending with** 90.**

- Like we did before, we will apply the following formula in cell
**F5**.

`=ARRAYFORMULA(VLOOKUP(“*90”,{TO_TEXT(C5:C10),B5:B10},1,FALSE))`

- Click
**ENTER**to insert the function and you will find the full date in your selected cell.

- If you want to look up other columns with the help of the year value, then you can apply this formula as well.

`=ARRAYFORMULA(VLOOKUP(1990,{YEAR(C5:C10),B5:B10},2,FALSE))`

- After applying the function, you will find the respective employee name ‘
**Oliver**’ whose date of birth is ‘**1990**’.

**Formula Breakdown**

**YEAR(C5:C10):**Here,**C5:C10**is the age range which represents in numeric form.**The YEAR function**converts the numeric year to text.**B5:B10:**It is the text range that represents the**Employee Name**.**“*2”:**This is the search key for the**VLOOKUP**function.**1:**It is the column number and**FALSE**is set as**is_sorted**to extract the exact value.**VLOOKUP(…):**Here, the function looks for a numeric value that ends with**2**whereas the**YEAR**function helps to convert the numeric value to the text.**ARRAYFORMULA(….):**Here, the**ARRAYFORMULA**function is used to support the non-array**YEAR**function.

**Read More: ****How to VLOOKUP All Matches in Google Sheets (2 Approaches)**

## Conclusion

After completing the article, we will hope that you can solve any type of problems related to **VLOOKUP** partial match in Google Sheets. For any queries feel free to comment and for deeper knowledge, you can also visit the **OfficeWheel** website.

## Related Articles

**Google Sheets Vlookup Dynamic Range****How to Use VLOOKUP with Named Range in Google Sheets****How to Use Nested VLOOKUP in Google Sheets****How to Check If Value Exists in Range in Google Sheets (4 Ways)****[Fixed!] Google Sheets If VLOOKUP Not Found (3 Suitable Solutions)****Combine VLOOKUP and HLOOKUP Functions in Google Sheets****How to Use VLOOKUP with Drop Down List in Google Sheets****How to Use VLOOKUP to Import from Another Workbook in Google Sheets**