Vlookup Partial Matches and Return Multiple Values [5 Ways]

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

1. Select cell C2.
2. Type this formula:=FILTER($A$2:$A$11, ISNUMBER(SEARCH(C2, $A$2:$A$11)))
3. 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:

1. Select cell C2.
2. Write down this formula:=IF(ISNUMBER(SEARCH($C$2, A2)), A2, “”)
3. Hit the ENTER key.
4. 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:

1. Choose Cell C2.
2. 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))), “”)
3. Press The ENTER key.
4. 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:

1. Select cell C2.
2. Type this formula:=FILTER($A$2:$A$11, ISNUMBER(SEARCH(C2, $A$2:$A$11)))
3. 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:

1. Select cell B12.
2. Use this formula :=TRANSPOSE(FILTER(A$2:A$10, ISNUMBER(SEARCH(B12, A$2:A$10))))
3. 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 TEXTJOIN 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 TRUE 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:

1. Choose cell B12.
2. Write this formula:=TEXTJOIN(” ,”, TRUE, IF(ISNUMBER(SEARCH(B12, A2:A11)), A2:A11, “”))
3. 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:

1. Select cell D4.
2. Use this formula :=VLOOKUP(D2&”*”,$A$2:$A$11,1,0)
3. 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:

1. Choose cell D4.
2. Type this formula :=VLOOKUP(D2&”*”,$A$2:$A$11,1,0)
3. 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:

1. 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.
2. 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))