This post explains that how to** get the first match that cell contains one of several values** from another range or a list in excel. before we talked the way that how to check a cell to see that if it contains one of several values in a range or list using the SEARCH function with ISNUMBER function. So now we still need to get the first match that found in the range or list.

Table of Contents

## Get First Match that Cell Contains one of several values

Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values from the range E1:E3. If it contains any of text string in range E1:E3, then it should return the first match value found in the range E1:E3, otherwise, it should return the #N/A error. you can use a combination of the INDEX function, the MATCH function, the ISNUMBER function and the SEARCH function to create a new excel array formula as follows:

=INDEX($E$1:$E$3, MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0))

**Let’s see how this formula works:**

**=SEARCH($E$1:$E$3,B1)**

The SEACH function returns position of the first character of find_text in a text string. And this formula will search each value in the range E1:E3 inside within_text in Cell B1, if it found the match, then returns position of each text string in Cell B1, otherwise, it will return the #VALUE! Error. So it will return an array result like this:

{1;7;12}

The returned result goes into the ISNUMBER function as its argument.

**=ISNUMBER(SEARCH($E$1:$E$3,B1))**

The ISNUMBER function will check if a cell contains a numeric value, and this formula will check each items of array result returned by the SEACH function, if the item is a numeric value, then return TRUE, otherwise, returns FALSE. So it will return another array result like this:

{TRUE;TRUE;TRUE}

**=MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0)**

The MATCH function will search the first value that is exactly equal to the lookup_value “TRUE”, then return the position of the first match. So this formula returns 1. It goes into the INDEX function as its row_num argument.

**=INDEX($E$1:$E$3, MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0))**

The index function will extract a value from the range E1:E3 based on the position number returned by the MATCH function. So it should be returned the first match value as “excel”.

### Related Formulas

- Check If Cell Contains All Values from Range

If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function… - Get last match that cell contains one of several values in a range

If you want to check a cell that if it contains one of several values in a range, you can use the SEARCH function to search find_text in a range inside a text string. Then we can use the LOOKUP function to get the last match values…. - Check if Cell contains one of many values from range

Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values in a range E1:E3. If it contains any of text string in range E1:E3, then it should be return TRUE value, otherwise, it should return FALSE….

### Related Functions

- Excel INDEX function

The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel SEARCH function

The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])… - Excel MATCH function

The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])…. - Excel ISNUMBER function

The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

## Leave a Reply

You must be logged in to post a comment.