# If Cell Contains Text Then Return Value in Another Cell Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for Excel formula if cell contains text then return value in another cell, then you’re in the right place. One of Excel’s most popular tasks is to check if a cell has a value based on a given condition. This value may be text, date, or any other numeric value. In this article, we’ll discuss the Excel formula if a cell contains text then return the value in another cell.

## 5 Ways to Form Excel Formula If Cell Contains Text then Return Value in Another Cell

We have a dataset of products e.g., laptops, desktops, and mobile. Now, we have to find different types of value with respect to the condition. Obviously, the following formulas might be helpful for finding the data in respect of different criteria.

### 1. Using IF Function

When you have the information that Desktop is delivered. Then you want to find the data for which city and cell, “Desktop is delivered”. You can use the IF function. The IF function is a logical function based on a given statement. There can be two results for an IF statement. The first result is true, the second is false when you compare.
The syntax of the function is

=IF (logical_test, [value_if_true], [value_if_false])

The arguments of the syntax are the following.

logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

Steps:

• Select a blank cell e.g., D5
• Type the formula
`=IF (C5="Dhaka", "Delivered", "Not Delivered")`

Here, C5 refers to the cell value and it is the city Dhaka, Delivered means if value is True or Not Delivered means if value is False.

• Press ENTER
• Use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5 cell like this.

• Eventually, we get the output like this.

### 2. Using ISNUMBER Function

The ISNUMBER function returns TRUE, and FALSE if not. You can use the ISNUMBER function combined with the SEARCH or FIND functions to verify that a cell has a desired value you want.

The syntax of the ISNUMBER function is

=ISNUMBER (value)

Here value is the input that you want to check
The syntax of the SEARCH function is

=SEARCH (find_text, within_text, [start_num])

The arguments of the syntax are the following
find_textThe text to find.
within_textThe text to search within.
start_num – [optional] Starting position in the text to search.

Steps:

• Select a blank cell like D5
• Type the formula
`=ISNUMBER (SEARCH ("Desktop", B5:B10))`

Here, Desktop is the text to find, B5:B11 is the cell range where you want to find the text.

• Secondly, press ENTER
• Thirdly, use the Fill Handle and get the output like this.

Here, any word containing Desktop whether it is lowercase or uppercase will give the output as TRUE.

But if you add any case-sensitive condition, you have to use the FIND function with the ISNUMBER function.
The syntax of the FIND function is

=FIND (find_text, within_text, [start_num])

In this case, write the formula in the D5 cell like this.

`=ISNUMBER(FIND("Desktop",@B5:B11))`

Lastly, press ENTER and use the Fill Handle to get the output like this.

The above figure reveals that the output ignores lowercase (i.e., desktop). So that this formula might be helpful for such types of situations.

### 3. Combination of IF-OR/AND-ISNUMBER Function

Assume you have a collection of some products where each product name consists of multiple information e.g., Laptop-Windows-HP that represents product category, operating system, and company name respectively.
Now if you want to find either Windows or Desktop. You can follow the steps below.
Well, before going to the main task, let’s introduce the OR function. The syntax of the OR function is.

=OR (logical1, [logical2], …)

The arguments of the syntax are as following:
logical1 – The first condition or logical value to evaluate.
logical2 – [optional] The second condition or logical value to evaluate

Steps:

• Select a blank cell like D5
• Type the formula
`=IF (OR(ISNUMBER(SEARCH("Windows", B5)),ISNUMBER(SEARCH("Desktop",B5))),"Available","Not Available")`

Here, B5 is the lookup value

• Secondly, press ENTER and use the Fill Handle.

Assume another case, when a customer wants such a type of product that must be under the Windows and Desktop category. You can follow the earlier formula, except that you have to use the AND function instead of OR function.
The syntax of AND function is.

=AND (logical1, [logical2], …)

The arguments of the syntax are the following
logical1 The first condition or logical value to evaluate.
logical2 [optional] The second condition or logical value to evaluate.

In this case, write the formula in the D5 cell like this.

`=IF(AND(ISNUMBER(SEARCH("Windows",B5)),ISNUMBER(SEARCH("Desktop",B5))),`
`"Available","Not Available")`

• Secondly, press ENTER.
• Thirdly, use the Fill handle and get the output.

Again, if you face case-sensitive issues, just use the same formula but replace the SEARCH function with the FIND function.
This time the formula will be

`=IF(AND(ISNUMBER(FIND("Windows”, B10)),ISNUMBER(FIND("Desktop",B10))),"Available","Not Available")`

Here, B10 is the lookup value.

• Similarly, press ENTER and use the Fill Handle to get other outputs.

### 4. Employing VLOOKUP Function

Imagine you have distributed promo codes of a fixed product item via the advertisement for a special discount. Later, if any customer wants any product using a promo code, how will you identify the product item?
Excel has a simple but effective function to find any vertical data and it is the VLOOKUP function.
The VLOOKUP function is an Excel function for vertically organized data searches in a table. The VLOOKUP function is compatible with both approximate and exact matching. The syntax of the function is

=VLOOKUP (value, table, col_index, [range_lookup])

The arguments of the syntax are the following
value – The value to look for in the first column of a table.
table – The table from which to retrieve a value.
col_index – The column in the table from which to retrieve a value.
range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

Steps:

• Select any empty cell e.g., C15
• Type the formula like
`=VLOOKUP(B15, B5:D11,2,FALSE)`

Here, B15 is the lookup value, B5:11 is the data range from you want retrieve data, 2 is column index which is the column number from the starting column of the dataset, and False means exact matching.

• Press ENTER
• Lastly, use the Fill Handle.

### 5. Formula with INDEX and MATCH Functions

In some cases, the dataset is not so simple as the previous ones. And we have to find our desired data by facing two or multiple criteria. In such a situation, a combination of INDEX and MATCH functions can produce impressive results in Excel.
The syntax of the INDEX function is

=INDEX (array, row_num, [col_num], [area_num])

The arguments of the syntax are the following
array – A range of cells, or an array constant.
row_num – The row position in the reference or array.
col_num – [optional] The column position in the reference or array.
area_num – [optional] The range in reference that should be used.
Besides the syntax of the MATCH function is

=MATCH (lookup_value, lookup_array, [match_type])

The arguments of the syntax are the following
lookup_value – The value to match in lookup_array.
lookup_array – A range of cells or an array reference.
match_type – [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.
The two functions can be used instead of the VLOOKUP function also.

• For this, select a blank cell. In this case, it is C14.
• Secondly, insert the formula in the C14 cell.
`=INDEX(C5:C11,MATCH("FGI39",B5:B11,0))`

Here, C5:C11 is the data from where you want to extract data, FGI39 is the lookup promo code, B5:B11 the cell range of promocode, and 0 is for exact matching.

• Thirdly, press ENTER and use the Fill Handle to get the output.

Now, move to the main point where we are going to find a value based on two criteria using INDEX and MATCH functions. Imagine, a customer wants to know the price of a Dell Laptop and of course, it will be of the 6th generation.
How can we find the value? Just follow the steps.

Steps:

• Firstly, select a blank cell. Here, it is D13.
• Secondly, insert the formula in the D13 cell.
`=INDEX(D5:D10,MATCH(1,(B13=B5:B10)*(C13=C5:C10),0))`

Here, D5:D10 is the Price data, B13 is the lookup value of the first criteria and B5:B10 is the data of first criteria, C13 is the lookup value for second criteria and C5:C10 is the data for second criteria. In this formula, a boolean logic is used to create a series of ones and zeros that match all 2 criteria and then The MATCH function to match the first 1.

• Press ENTER (if you are a Microsoft 365 user) or CTRL + SHIFT + ENTER (for other Excel versions as it is an array formula).

## Things to Remember

• While inserting the INDEX formula, be careful about the row and column number. Besides, input the right column index while inserting the VLOOKUP formula.
• More importantly, be careful about the file name, file location, and excel extension file name.

## Conclusion

Now you’ve got these formulas to return value in another cell if the cell contains the text. I believe that you understand all the processes well. If you have any confusion or query, please share them below in the comments section. Thanks for being with us.