Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

=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.

excel formula if cell contains text then return value in another cell

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

Using IF Function

  • Eventually, we get the output like this.

Read More: How to find text in an Excel range & 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

=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.

Using ISNUMBER Function

  • 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.

excel formula if cell contains text then return value in another cell

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.

excel formula if cell contains text then return value in another cell

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


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

Combination of IF-OR/AND-ISNUMBER function

  • Secondly, press ENTER and use the Fill Handle.

excel formula if cell contains text then return value in another cell

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.

excel formula if cell contains text then return value in another cell

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.

excel formula if cell contains text then return value in another cell

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

Read More: If Cell Contain Word Then Assign Value in Excel (4 Formulas)


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.

Employing VLOOKUP function

  • Press ENTER
  • Lastly, use the Fill Handle.

excel formula if cell contains text then return value in another cell

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

=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.

Applying INDEX Function

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

excel formula if cell contains text then return value in another cell

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.

excel formula if cell contains text then return value in another cell

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

Read More: How to Return Value If Cells Contain Certain Text from a List


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo