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.

**– [optional] The value to return when logical_test evaluates to TRUE.**

*value_if_true**– [optional] The value to return when logical_test evaluates to FALSE.*

**value_if_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.

**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_text – **The text to find

**.**

**The text to search within.**

*within_text*–*[optional] Starting position in the text to search.*

**start_num –****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.

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

*– [optional] The second condition or logical value to evaluate*

**logical2****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.

**[optional] The second condition or logical value to evaluate.**

*logical2*–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.

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

*– The table from which to retrieve a value.*

**table***– The column in the table from which to retrieve a value.*

**col_index***[optional] TRUE = approximate match (default). FALSE = exact match.*

**range_lookup –****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**.

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

*– The row position in the reference or array.*

**row_num***– [optional] The column position in the reference or array.*

**col_num***– [optional] The range in reference that should be used.*

**area_num**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.

**– A range of cells or an array reference.**

*lookup_array***– [optional]**

*match_type***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).

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