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

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.

Download Practice Workbook

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

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

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.

Read More: Find Text in Excel Range and Return Cell Reference (3 Ways)

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

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

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

Steps:

• Select a blank cell like D5
• Type the formula

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

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

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.

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel

Similar Readings

• How to Sum If Cell Contains a Text in Excel (6 Suitable Examples)
• Return TRUE If Cell Contains Text in Excel (8 Easy Ways)
• Use IF-THEN Statements with Text in Excel (7 Examples)
• How to Use VLOOKUP If Cell Contains a Word within Text in Excel

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.

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

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.

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.

• 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

Here, B10 is the lookup value.

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

Read More: How to Assign Value If Cell Contains Word in Excel (4 Easy Ways)

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

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

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.

Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel

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

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

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.

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.

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.

Related Articles

• If Cell Contains Text Then Copy to Another Sheet in Excel
• Check If Cell Contains Partial Text in Excel (5 Ways)
• Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
• Excel If Cell Begins with Certain Text Then Return Value
• How to Use IF & OR Functions with Text in Excel (3 Examples)
• If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)
• How to Use “Not Equal to” Operator for Text in Excel (5 Examples)