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

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, I am going to show some Excel formulas to return value in another cell if the cell contains text.

Download Practice Workbook

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

We have a dataset of products e.g., laptop, desktop, 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.

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") where 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 Tool for getting other cell values.

Using IF Function If Cell Contain Text

Read More: How to find text in an Excel range & return cell reference (3 ways)

2. Using ISNUMBER Function

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

The syntax of ISNUMBER 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)) where “Desktop” is the text to find, B5:B10 is the cell range where you want to find the text.
  • Press Enter

Using ISNUMBER Function If Cell Contain Text

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

The syntax of the FIND function is

=FIND (find_text, within_text, [start_num]) 

Using ISNUMBER Function_case-sensitive If Cell Contain Text

The above screenshot 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 a small OR function. The syntax of the 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") where B5 is the lookup value
  • Press Enter

Combination of IF-OR-AND-ISNUMBER Function If Cell Contain Text

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 AND function instead of OR.

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.

Combination of IF-OR-AND-ISNUMBER Function If Cell Contains Text

Again, if you face case-sensitive issues, just use the same formula except FIND function instead of SEARCH function.

This time the formula will be

=IF(AND(ISNUMBER(FIND("Windows”, B10)),ISNUMBER(FIND("Desktop",B10))),"Available","Not Available") where B10 is the lookup value.

Combination of IF-OR-AND-ISNUMBER Function If Cell Contains Text

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.

VLOOKUP is an Excel function for vertically organized data searches in a table. VLOOKUP 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) where 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

VLOOKUP Function If Cell Contains Text

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

5. Applying INDEX Function

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 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 and insert the formula like =INDEX(C5:C11,MATCH(“FGI39”,B5:B11,0)) where 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, 0 is for exact matching.

Applying INDEX function

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:

  • Select a blank cell
  • Insert the formula like =INDEX(D5:D10,MATCH(1,(B13=B5:B10)*(C13=C5:C10),0)) where D5:D10 is the price data, B13 is the lookup value of 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 MATCH 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).

Applying INDEX function with two criteria

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

Things to Keep in Mind

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 below in the comments section.

Thanks for being with me.


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