# Vlookup Partial Matches and Return Multiple Values [5 Ways]

To vlookup partial matches and return multiple values, you can follow the steps below:

- Select cell
**C2.** - Type this formula:
**=FILTER($A$2:$A$11, ISNUMBER(SEARCH(C2, $A$2:$A$11)))** - Press the
**ENTER**button.

This formula checks a list **(A2:A11)** for cells that contain the text in cell **C2** and includes them in the filtered result. It does this by searching for the text in **C2** within the list and only keeping the matching cells.

## Vlookup Partial Matches & Return Multiple Values with IF Function

In Excel, you can vlookup for a partial match using a combination of the **IF** and **ISNUMBER** functions, as well as the **SEARCH** function. You can use these functions to search for a particular text within a given range and return a matching value.

**Syntax**

=IF(ISNUMBER(SEARCH(find_text, within_text)), [value_if_true], [value_if_false])

**Formula**

=IF(ISNUMBER(SEARCH($C$2, A2)), A2, "")

**Formula Breakdown**

- The formula is used to check if the text in cell
**C2**is found within the text in cell**A2**. If a match is found, it returns the text from cell**A2**; otherwise, it returns an empty string**(“”)**. - The
**ISNUMBER(SEARCH($C$2, A2))**part checks whether the text in cell**C2**is a substring of the text in cell**A2**. - If it is,
**ISNUMBER**returns**TRUE**, and the**IF**function returns the text from**A2**; if not, it returns an empty string, effectively filtering the values in column**A**based on whether they contain the text in**C2**.

To vlookup partial matches and return multiple values, using **IF, ISNUMBER,** and **SEARCH** functions, follow these steps:

- Select cell
**C2.** - Write down this formula:
**=IF(ISNUMBER(SEARCH($C$2, A2)), A2, “”)** - Hit the
**ENTER**key. - Apply the
**Fill Handle**to copy down the formula.

When I search for ‘Samsung,’ this function attempts to find partial matches throughout the dataset wherever ‘Samsung’ is present. When a partial match is found, the function returns all the matching results.

## Vlookup Partial Matches & Return Multiple Values by Multiple Functions

You can employ a combination of functions like **IFERROR, INDEX, SMALL, ISNUMBER, SEARCH, MIN,** and **ROW** to locate partial matches and retrieve multiple results.

Initially, the **SEARCH** function attempts to identify partial matches with the provided value. Subsequently, the **ISNUMBER** function generates an array of** ‘TRUE’** and **‘FALSE’** values, indicating whether a partial match was found. Lastly, the **IFERROR** function is used to gracefully manage situations when no match is found, replacing them with an empty string.

**Syntax**

=IFERROR(INDEX(array, SMALL(IF(ISNUMBER(SEARCH(find_text, within_text)), ROW(reference)-MIN(ROW(reference))+1), ROW(reference))), "")

**Formula**

**=IFERROR(INDEX(A$1:A$11, SMALL(IF(ISNUMBER(SEARCH($C$2, A$1:A$11)), ROW(A$1:A$11)-MIN(ROW(A$1:A$11))+1), ROW(A1))), "")**

**Formula Breakdown**

This formula combines several Excel functions to find partial matches and return multiple results.

- It searches for the text in cell
**C2**within the range**A$1:A$11**. If a partial match is found, it returns the corresponding value from**A$1:A$11**. - The
**INDEX**and**SMALL**functions work together to retrieve the matched values, and**ROW(A1)**increments as the formula is copied down to generate a dynamic list. - The
**IFERROR**function handles cases where no match is found, replacing them with an empty string, effectively creating a filtered list of values from**A$1:A$11**based on the partial match in**C2**.

Now, use the combination **IFERROR, INDEX, SMALL, ISNUMBER, SEARCH, MIN,** and** ROW** functions to find partial matches and return multiple results. Follow these steps:

- Choose Cell
**C2.** - Use this formula:
**=IFERROR(INDEX(A$1:A$11, SMALL(IF(ISNUMBER(SEARCH($C$2, A$1:A$11)), ROW(A$1:A$11)-MIN(ROW(A$1:A$11))+1), ROW(A1))), “”)** - Press The
**ENTER**key. - Copy down the formula.

When you search for a value, this formula attempts to find all partial matches and displays them in a sequential order.

## Vlookup Partial Matches & Return Multiple Values Using FILTER Function

You can use **FILTER, ISNUMBER,** and **SEARCH** in Excel to search for partial matches in a dataset and get multiple matching values that match your search criteria. This combination of functions allows users to find exact partial matches in the dataset and get several matching values.

**Syntax**

**=FILTER(array, ISNUMBER(SEARCH(find_text, within_text)))**

**Formula**

=FILTER($A$2:$A$11, ISNUMBER(SEARCH(C2, $A$2:$A$11)))

**Formula Breakdown**

- This formula uses the
**FILTER**function in Excel to extract values from the range**A$2:$A$11**based on a search term in cell**C2**. - The
**ISNUMBER(SEARCH(C2, $A$2:$A$11))**part checks each cell in the range**A$2:$A$11**for a partial match with the text in**C2,**returning an array of**TRUE**and**FALSE**values, where**TRUE**indicates a match was found. - The
**FILTER**function then uses this array as a criteria to filter and return all values from the range**A$2:$A$11**where a partial match is detected, effectively providing a dynamic list of results that meet the specified criteria.

Follow these steps to vlookup partial match and return multiple values using **FILTER, ISNUMBER,** and **SEARCH** functions:

- Select cell
**C2**. - Type this formula:
**=FILTER($A$2:$A$11, ISNUMBER(SEARCH(C2, $A$2:$A$11)))** - Press the
**ENTER**button.

Ultimately, all partially matched values have been located.

## Vlookup Partial Matches & Return Multiple Values with TRANSPOSE Function

To retrieve multiple results, you can utilize a combination of **TRANSPOSE, FILTER, ISNUMBER,** and **SEARCH** functions. This formula will generate an array of all the partial matches from the column you’re searching for, and the **TRANSPOSE** function will neatly display each result in individual cells.

The formula will adapt dynamically, arranging all matching values horizontally in separate cells for easy viewing.

**Syntax**

**=TRANSPOSE(FILTER(array, ISNUMBER(SEARCH(find_text, within_text))))**

**Formula**

**=TRANSPOSE(FILTER(A$2:A$10, ISNUMBER(SEARCH(B12, A$2:A$10))))**

**Formula Breakdown**

This formula combines several Excel functions to search for partial matches and neatly display them in separate cells.

- It starts by using the
**SEARCH**function to check for the presence of the text in cell**B12**within the range**A$2:A$10.** **ISNUMBER**then generates a**TRUE/FALSE**array, where**TRUE**indicates partial matches.- The
**FILTER**function extracts all values from**A$2:A$10**where a partial match is found. - Finally, the
**TRANSPOSE**function arranges these values horizontally in separate cells, creating a dynamic list of results that correspond to the search term in cell**B12**.

To vlookup partial match and return multiple results using the **TRANSPOSE, FILTER,** and **SEARCH** functions combined, use the following steps:

- Select cell
**B12.** - Use this formula :
**=TRANSPOSE(FILTER(A$2:A$10, ISNUMBER(SEARCH(B12, A$2:A$10))))** - Press the
**ENTER**button.

Ultimately, this formula searches to identify partially matched values and neatly presents them in separate cells horizontally. It results in a dynamic list that precisely corresponds to the search term **“Apple”**.

## Vlookup Partial Matches & Return Multiple Values by TEXJOIN Function

By combining the **TEXTJOIN, IF, ISNUMBER,** and **SEARCH** functions, you can efficiently discover partial matches and retrieve multiple values. This formula uses the T**EXTJOIN** function to assemble a concatenated list of values that align with your search criteria, offering a versatile method for returning multiple partial matches.

**Syntax**

**=TEXTJOIN(delimiter, TRUE, IF(ISNUMBER(SEARCH(find_text, within_text)), [if_the_value_true], [if_the_value_false]))**

**Formula**

=TEXTJOIN(" ,", TRUE, IF(ISNUMBER(SEARCH(B12, A2:A11)), A2:A11, ""))

**Formula Breakdown**

This formula integrates several Excel functions to locate partial matches and consolidate them into a single text string.

- Initially, the
**SEARCH**function examines whether the text in cell**B12**can be found within the range**A2:A11**, resulting in an array with position numbers or errors.**ISNUMBER**subsequently interprets this array, distinguishing partial matches by returning**TRU**E values. - The
**IF**function then assembles an array comprising values from**A2:A11**where a partial match exists and replaces non-matching cells with an empty string. - Finally,
**TEXTJOIN**combines these values using a comma and space separator**(“, “)**while ignoring empty cells marked as**TRUE**, yielding a cohesive list of partial matches separated by commas.

Combine the **TEXTJOIN, IF, ISNUMBER,** and **SEARCH** functions to vlookup partial matches and return multiple values. Here are the steps to accomplish this:

- Choose cell
**B12.** - Write this formula:
**=TEXTJOIN(” ,”, TRUE, IF(ISNUMBER(SEARCH(B12, A2:A11)), A2:A11, “”))** - Hit the
**ENTER**button.

Ultimately, with the use of this formula, you can discover all the partial matches consolidated into a single cell, neatly separated by commas.

## Find Partial Matches and Return Single Value Using VLOOKUP Function

You can use the VLOOKUP function to find partial matches and return single values.

**Syntax**

=VLOOKUP(lookup_value,table_array,Col_index_num,[range_lookup])

**Formula**

=VLOOKUP(D2&"*",$A$2:$A$11,1,0)

To find a partial match and return a single value by using **VLOOKUP **function, follow the steps below:

- Select cell
**D4.** - Use this formula :
**=VLOOKUP(D2&”*”,$A$2:$A$11,1,0)** - Press the
**ENTER**button.

Finally, you can obtain your expected result by applying this formula.

## VLOOKUP Partial Matches and Return a Single Value by CHOOSE Function

You can utilize the combination of** VLOOKUP** and **CHOOSE** functions to partially match and return a single value.

**Syntax**

=VLOOKUP(lookup_value,CHOOSE({1},$A$2:$A$11,$A1:$A$2),Col_index_num,[range_lookup])

**Formula**

=VLOOKUP(D2&"*",CHOOSE({index_num},value_1,[value_2]),1,0)

To find a partial match and return a single value by using the combination of **VLOOKUP **and** CHOOSE **Function, follow the steps below:

- Choose cell
**D4.** - Type this formula :
**=VLOOKUP(D2&”*”,$A$2:$A$11,1,0)** - Hit the
**ENTER**button.

Finally, by using this formula, you can get the desired outcome.

## Conclusion

In this article, the aim is to demonstrate how to Vlookup partial matches and retrieve multiple values when a partial match is detected. Sometimes, you might need to search for specific values in Excel that aren’t exact matches but share common terms, such as words, names, or other related criteria. Utilizing one of these five methods, you can effortlessly identify partial matches and retrieve all corresponding values.

## Frequently Asked Questions

### What is the difference between VLOOKUP and index match?

**VLOOKUP** and **INDEX MATCH** are Excel functions for data retrieval. Here’s the difference between them:

**VLOOKUP**searches a specified column for a value and returns a corresponding result. INDEX MATCH, a two-step process, first identifies the position of a value and then retrieves the data.- While
**VLOOKUP**is straightforward,**INDEX MATCH**is more versatile, allowing for dynamic ranges and bidirectional searches.

### How do you do a VLOOKUP with partial values?

Performing a** VLOOKUP **with partial values in Excel involves using wildcard characters, such as the asterisk **(*)**, to match partial text.

For example, to find a partial match for **‘abc’ **in a column containing values like **‘abc123’** or **‘xyzabc’**, the formula would be: **=VLOOKUP(“abc”, A1:B10, 2, FALSE)**.

The asterisks act as placeholders for any characters before or after ‘abc’, allowing for partial matching in the VLOOKUP function.

### Can you use Xlookup for partial match?

Microsoft Excel’s **XLOOKUP** function doesn’t have a built-in option for partial matches like the **VLOOKUP** or **HLOOKUP** functions do with wildcards. **XLOOKUP** is primarily designed for exact matches.

If you need to perform a partial match using **XLOOKUP**, you might need to modify your data to accommodate the exact matching requirement or consider using other functions like **INDEX** and **MATCH** in combination.

For example, you could use the following formula to perform a partial match:

**=INDEX(return_range, MATCH("*" & partial_value & "*", lookup_range, 0))**