If cell contains text from a list then return value. Syntax for IF CONTAINS in Google .
If cell contains text from a list then return value Dec 8, 2023 · To test if a cell contains one of many strings, you can use a formula based on the SEARCH, ISNUMBER and SUMPRODUCT functions. If all of these apply, we’ll return “ Satisfy “. If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. See how to apply Excel conditional formatting, copy or delete rows based on cell value. It is a common practice. In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if the substring in cell C5 is found in the text from cell B5. Jan 16, 2020 · I've seen many posts about the subject, but in general the question has been to return the value of a cell if the cell contains text from a list. Trying to write a formula that’ll return a value from a list if the cell partially contains text from one of the options in said list. Sep 1, 2023 · This tutorial explains how to check if a cell contains specific text in Excel and return a value, including several examples. . Method 1: Check if cell value contains text from list (partial match) and return TRUE or FALSE This first method is actually the most important method to understand because it’s building block for methods #2 and #3 where we want to return another column from the lookup list. The following example shows how to use this formula in practice. May 23, 2023 · The tutorial explains the syntax and basic uses of the IF function in Excel. e. I have used the following formula for that: =IF(OR( Aug 5, 2021 · Excel - if cell contains text in a list then return value of another cell Asked 4 years, 3 months ago Modified 1 year, 7 months ago Viewed 19k times In this article, we will learn How to look up cells having certain text and return the Certain Text in Excel. " If you want to check other cells for the presence of keywords, simply copy the formula in cell C1 and paste it into the appropriate cells. Nov 30, 2022 · This tutorial explains how to search for a values in a list in Excel and return either "yes" or "no" if the value exists in the list or not. To fix the body, click edit. This combination of functions works perfectly. Master flexible conditional logic Jan 22, 2025 · This Excel formula quickly checks if cells contain dates then return values. Mar 17, 2023 · See how to do VLOOKUP with IF statement in Excel and how to use If Isna Vlookup formulas to replace #N/A errors with your own text, zero or blank cell (empty string). Let us say, we have input data in Cell A1 and We want to Return Value in Another Cell B1. For example, the original cell cont Nov 9, 2021 · To test a cell for one of several strings, and return a custom result for the first match found, you can use an INDEX / MATCH formula based on the SEARCH function. Download our Excel workbook, modify data & exercise! Oct 23, 2023 · Explanation The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. To check if a cell contains a partial text in Excel, you need to create a formula, with the help of IF, COUNTIF, and wildcard characters. Sep 7, 2021 · The Excel ISTEXT function returns TRUE when a cell contains a text value, and FALSE if the cell contains any other value. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. Nov 10, 2020 · Here is the the Excel formula to check if cell contains specific text then return value. I am a bot, and this action was performed automatically. Last edited by MushroomFace; 03-07-2015 at 07:30 PM. One of the major uses of Excel is to conduct efficient searches. May 24, 2024 · Here, I have explained how to create IF THEN statements in Excel with text. " In this example, cell C1 will display "Yes" because cell B1 contains the keyword "apple. Note the SEARCH function is not case-sensitive. Download the workbook and practice yourself. If you find text, return a "Use Case" in the Product column. Mar 14, 2023 · The tutorial shows how to make Excel IF statement with wildcard text and get it to work for partial matches. The formula in F5 is: Jul 15, 2020 · If the column contains text/number, then return value Excel 2016: In Sheet1 I have a complete list of products in which I need to somehow mark the products that are also in Sheet2, preferably so that the value is displayed in the next blank column, based on a comparison of product numbers from the following columns: Count Cells that contain specific text : Count number of cells if cell contains given text using one formula in Excel. Jul 2, 2024 · 5 Methods to Return TRUE If a Value Exists in a Column in Excel Method 1 – Use a Simple Formula to Find TRUE If the Columns Match Steps: Use the following formula in the first cell of the result column (here, Cell D5). We can use COUNTIF and OR function to check the array of values in a Cell and return the given Value. Chens Jin-Xing Moh Kok Heng Svalinn Tan Siow Hoon Terry AIA Singapore Please help. a substring), you can use the SEARCH function together with the ISNUMBER function. Aug 29, 2024 · If Cell Contains Text Then Return Value in Another Cell Excel is a very smart spreadsheet software. 2 days ago · How to Check If a Cell Contains Text Then Return Value in Excel Excel is a powerful tool used for data management, analysis, and reporting. Wildcard characters help you define the partial text, then COUNTIF checks for partial value, and IF returns the result according to that. Nov 2, 2011 · Need a value 1 if the cells in a particular column contains the below texts. The formula I am using seems to work for the top cell only. For example, to return "Yes" if column A contains the word "dog" in any cell and "No" if not, you can use a formula like this: = IF (COUNTIF (A:A,"*dog*"),"Yes","No") Jun 22, 2023 · This tutorial explains how to use the following formula in Excel: if cell equals value from list then do something. See below for a case-sensitive Aug 24, 2025 · Learn how to return a value in another cell if a cell contains text in Google Sheets using IF with SEARCH, EXACT, or REGEXMATCH functions. A common requirement when working with data sets is to evaluate whether a cell contains text and, based on the Follow the submission rules -- particularly 1 and 2. Aug 10, 2023 · Here, in this article, I'll discuss 7 methods of how to return value in any cell if another cell contains any specific text in Google Sheets. Dec 12, 2023 · This tutorial explains how to use an "if contains" formula in Power BI, including an example. Discover multiple methods using formulas or Kutools for Excel. Here, we'll use DATEVALUE, DATE, TODAY, and COUNTIFS functions. Jun 16, 2024 · Method 1 – Use the IF Function to Check If the Cell Contains Text and Return a Value in Excel You can use the IF function to check if the cell contains text and then return a value. Often, we need to search for a text in By returning value in Excel, if a cell contains text from a list, you can categorize data, assign labels, or perform conditional calculations based on text values. This function will search for a specific value in the first column of a specified range and return a result from another column in the same row. Excel file is freely available to download for you. There isn’t an exact IF CONTAINS Google Sheets function. Steps: Select the first result cell Oct 16, 2025 · This formula will return the value of "Valid" to the output cell since the A2 cell contains one of the text values searched for. The remaining examples show formulas that check if a cell contains text and also identify which text the cell contains. You will learn how to create an 'If then' formula for text, numbers, dates, blank cells as well as how to write multiple IF statements. Nov 21, 2025 · Learn how to check if a value exists in a range in Excel by using Match, VLOOKUP, or Conditional Formatting with our easy-to-follow guide. Sep 12, 2023 · Examples of Excel formula to sum or count if cell contains any text or specific text. May 21, 2024 · This is the formula used in cell D5 of the example. Then Column E needs to fill the brand associated with the cell in Column C (Item description). I'd like to flag all Messages from Message table which contain any keyword from Keyword table and return that Keyword as new column in Message table as an ouput. Part numbers have different lengths and compositions and they are all mixed up. Use the following formula in E5 to check whether the cell D5 contains the specific text Passed. Contains a function that you can use to test whether a sub-string is in the string or not. Also, I have described 7 suitable methods. Jun 28, 2024 · Method 1 – Using the IF Function to Check If a Cell Contains an Exact Text in Excel The Grade column has Passed or Failed in every cell. Jul 3, 2024 · Example 5 – Using a Nested IF Function for a Range of Values in Excel We’ll check whether the price is higher than $30, then check if the number of books is higher than 15. " Otherwise, it will return "No. May 8, 2024 · Here, we learned Excel if cell begins with certain text then return value and covered every possible way to solve this issue. Analyze the situation with suitable examples involving Macro, UDF, and UserForm. I'm hoping to use a formula that says, "Look in the Use Cases column for any text. Apr 14, 2025 · Learn how to check if a cell contains any text using Excel formulas like IF, ISTEXT, LEN, and COUNTIF. May 4, 2024 · To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. Jul 4, 2024 · This article will enlighten you about the easiest 8 ways using functions and VBA code about Excel check if value exists in range. I need a formula that searches row A for a specific word in the cell (cell contains multiple words) and, if found, returns the value of a cell in the same row but different column. As the formula is copied down, it returns "x" if an email address contains either "abc" or "xyz" and an empty string ("") if not. This article demonstrates different formulas for different Excel versions. Aug 7, 2017 · Here is the Excel formula if cell contains text then return value in another cell. Feb 13, 2022 · Hi Experts, How to return value if cell contains any text in another column in the same table ? There are duplicates value in Group 1 & Group 2. Example 7: If the Cell Contains Several of Many Text Strings, Then Return a Value Aug 3, 2024 · In this article, you will find 4 simple ways to check if a cell contains one of several values in Excel using different functions. In Power Query, you can use the Text. How to get result in Sheet1 column C for each product based on its Text description and appropriate values defined in Sheet2? Jul 22, 2025 · Check if a cell contains one of several values from a list with formulas To determine if a cell contains any of the text values from another range, you can use an array formula that works efficiently even for large datasets. Thank you. Dec 4, 2011 · IF a cell contains text from a drop down list, then Hello, I am having trouble getting the IF function to work! I am trying to to input numbers into a cell automatically when a certain cell has a text input selected from a list that I have set up using cell validation. Apr 17, 2025 · Learn how to return values in another cell in Excel based on specific text in a cell. Ex: if cell B2 partially contains “Happy” then return the value from the list with the word Happy in it. Failing to follow these steps may result in your post being removed without warning. If A1 or B1 contain "text value 1" return C1. Jan 18, 2022 · 1) I have a table with two columns - one with various text options (for easy, let's call these "flavours"), the other with the "type" or category that these text options would fit under 2) On a separate sheet, I want to see if a cell contains one of the "flavours", and subsequently return the "type" associated with it Aug 5, 2024 · This formula searches for the value in cell A1 within the range D1:E3 and returns the corresponding text from the second column of the range. Jan 18, 2022 · 1) I have a table with two columns - one with various text options (for easy, let's call these "flavours"), the other with the "type" or category that these text options would fit under 2) On a separate sheet, I want to see if a cell contains one of the "flavours", and subsequently return the "type" associated with it Aug 28, 2022 · This article shows how to use Excel if cell contains date then return value. Jun 12, 2020 · Based on the description of the product (shown in Column C), if this cell contains ANY of the listed brands in Column D. In the example shown, the formula in C5 is: {=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0))} where things (E5:E8 ) and results (F5:F8) are named ranges. How to lookup cells having certain text and returns the Certain Text in Excel : find cells if cell contains certain text and returns required results using the IF function in Excel. Oct 27, 2022 · Hello, I'm looking for a formula that would return the text in a cell adjacent to a search result. Aug 17, 2025 · When we work with large datasets, sometimes we need to find a specific text within a range and then return its cell reference. Sep 29, 2023 · I'm needing to write a formula to determine if one cell contains any specific text from another list, and then return a value based on which text is in the cell. If cell contains one of many text strings, then return a value This formula should be used if you’re looking to identify cells that contain at least one of many words you’re searching for. Jul 2, 2024 · In this article, you will find 9 easy ways to check if a cell contains text then return value using different functions in Excel. Feb 18, 2019 · In EXCEL I would like to use a formula that would read what is in cell A1 and then return a specific drop down list (already created in another sheet). If Group 2 contain any value in Group 1, return value as "Yes" otherwise blank. Apr 19, 2024 · Learn 5 super easy methods to check if a cell contains partial text in Excel. Dec 14, 2023 · If column contains value then If you need to check a column for a specific text value, the simplest approach is to switch to the COUNTIF function with wildcards. You are free to adjust the IF formula to return whatever values you like. Nov 13, 2018 · Is there a way to check a text field to see if it contains any of the strings from a list? Example Strings to Check: The raisin is green The pear is red The apple is yellow List Example to Validate Nov 5, 2023 · To check if a cell contains text from a list in Excel, you can use the VLOOKUP, or Vertical Lookup, function. One of its key strengths lies in how it allows users to perform logical tests, manipulate data, and automate tasks via formulas and functions. Learn them, download the workbook and practice. Like searching for a specific text or just text in Excel and returning another value elsewhere 🧐 This process (if successfully worked Jan 26, 2022 · This tutorial explains how to check if a cell contains text from a list in Excel, including an example. We will use the combination of IF, ISNUMBER and SEARCH functions in Microsoft Excel. Jul 21, 2025 · Unlock Excel's IF Cell Contains power! Get 25 real-world formulas & examples to check for partial text, multiple words, numbers, & more. Mar 6, 2015 · If cell contains text from a list, return value. And we need to check which are the names with the female suffix. Jun 23, 2024 · This article shows 4 easy ways to find if a range of cells contains specific text in Excel. Mar 4, 2021 · 0 I have a list of products with Material no and Text description as visible in Sheet1 image. Scenario : Identify particular text in a cell or different word in given cells. In the example shown, the data validation applied to C4 is: =IF(C4="See full list",long_list,short_list) This allows a user to select a city from a short list of options by default, but also provides an easy way to view and select a In this article we will learn how to specified number, if a cell contains certain text. Includes examples, VBA code, and a downloadable file. Aug 16, 2025 · Learn how to check if a cell contains text from a list in Excel using SEARCH, COUNTIF, XLOOKUP, INDEX, and more powerful functions. Group 1 Group Apr 9, 2024 · I am trying to get a formula that would evaluate the cells in the column 'data' to see if they contain any of the words contained in the column 'List', then if true return the word "Groceries" or otherwise leave the cell blank in the 'Output' column. Otherwise, we’ll return “ Does not Satisfy “. Example: IF The cell A1 contains the number Nov 21, 2025 · Searching a massive spreadsheet for specific text can be annoying when you do it manually. To fix your title, delete and re-post. Mar 15, 2021 · IF cell contains, THEN return certain value with more values and return possibilities Asked 4 years, 8 months ago Modified 4 years, 8 months ago Viewed 1k times For example if cell with text “Amazon*F7H76” contain “Amazon”, then return “Amazon” and do on Jan 20, 2023 · Hi! I am working on a formula for a sheet that has a product column, a Use Cases column and a Tool column. If you can't find any words, then return the value in the Tool column in… Jul 4, 2024 · 2 suitable uses of VLOOKUP function if a cell contains a word within text in Excel. Dec 13, 2021 · Return a value from a list depending if value in another cell contains this Hi Excel community, I am trying to build a large data file decoding part numbers. If cell B1 contains any of the keywords from the list, the formula will return "Yes. However, you can use a clever combination of a few formulas to automate the process. I use the formula =large (S76:s146,1) to find which item sold the most and return that value to J143. Apr 6, 2019 · Here is the Excel Formula to check if cell contains text from list then return value. In the below example, we have a list of names with the suffix for male and female. Nov 10, 2020 · Excel Formula to check if a Range Contains specific text and return a value. Dec 28, 2022 · In this example, if cell A1 contains any of the text values in the range E2:E8 then it will return TRUE, otherwise it will return FALSE. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text. Nov 16, 2021 · I'm trying to figure out how to perform this calculation. If the condition is met, it returns a TRUE value, otherwise it returns a FALSE value. Feb 7, 2024 · Learn how to set up an "IF contains" formula in Excel. For example, this is useful in inventory tracking, employee databases, or maybe any searchable list where you need to have the exact location of a value rather than just the value itself. Jul 6, 2024 · Excel VBA analysis: If a cell contains a value then. And if you’re not using it to automate your tasks smartly – you’re not doing it right. Note: the IF function simply leaves an "x" in a cell as a marker. Every part of the text has a defined value in another sheet. Check for validate date ranges, highlight rows, sum related numbers, add days, or compare dates. Sep 30, 2024 · Unleash the power of Excel with a simple formula: if a cell contains specific text, return a value. Another example is when users want to dynamically Feb 12, 2025 · To check if a cell contains specific text (i. For example finding the department ID from a database. Master the IF statement in Excel and discover how to use the "IF contains" function effectively. A common scenario many users encounter is wanting to check if a cell contains any text from a list and then return a corresponding value. Jul 3, 2024 · The article explains five methods based on formulas to lookup a value in a column and to return the value of another column in Excel. How do I return the corresponding cell from column Q to I143? Thank you in advance for your response. May 12, 2017 · HOW TO: "If cell contains specific text then return specific text" I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell. Oct 17, 2023 · A number of 'Excel if cells contains' formula examples show how to return some value in another column if a target cell contains specific text, any text, any number or any value at all (not empty cell), test multiple criteria with OR as well as AND logic. We will check whether a cell contains Passed and add a remark in a seocnd Remarks column. Otherwise, it returns FALSE. Jan 18, 2024 · 'If cell contains, then return value – multiple conditions A single ‘if cell contains’ statement is easy, right? But what if you need to check multiple conditions in the same formula and return different results? That is, if a cell contains some text, then return something, if it contains another text, then return something else, and so on. Apart from this function, we can also use the IF statement to test for a substring. Mar 21, 2021 · To determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function and wildcards. Please contact the moderators of this subreddit if you have any questions or concerns. It will Check any Cell in a Range for a specific text and return True or False. Aug 6, 2024 · Here, you will find 7 ways to check if a cell contains text and then return value with multiple conditions in Excel using different functions. Mar 4, 2020 · If text contains value from list then return that value 03-04-2020 02:13 AM Hi, I have 2 columns in 2 tables - Message [Message] and Keyword [Keyword]. After that, we’ll check if the author’s name starts with the letter C. I think you must have thought to do it manually but time constraint. The first example shows how to check if text in the list is in the cell. This article serves as a guide to help you use some formulas to search a cell to see whether it contains a specified value. For example: column Q has the list of the items sold, column S is the number of items sold. 2 days ago · Among its numerous functionalities, the ability to perform conditional logic, particularly returning specific values based on cell contents, is crucial for automation and dynamic data processing. Insert the following formula in cell D5 and press Enter. Jan 30, 2025 · This article demonstrates several techniques to check if a cell contains text based on a list. This guide offers a comprehensive yet easy-to-follow tutorial, perfect for beginners and experienced users, to master this useful function and boost your spreadsheet skills. Jul 2, 2024 · In this article, you will get the easiest ways to check if a value is in list in Excel. Do not suggest split into two table and merge queries. The formula in C5, copied down, is: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0 where things is the named range E5:E7. Jan 18, 2024 · This tutorial explains how to check if a cell value exists in another sheet in Excel, including an example. For example, if you have a list of names in column A and you want to know if a specific cell contains one of those names, you can use the Jul 3, 2024 · Learn how to check if one cell equals another then return another cell in Excel in many ways with relevant examples using functions. Syntax for IF CONTAINS in Google Jul 20, 2023 · This tutorial explains how to return a value in Excel if a cell contains a number, including an example. This is an array formula and must be entered with Control + Shift Apr 16, 2024 · Method 2 – If Cell Contains Specific Text Then Add Text in Another Cell Using IF Function Suppose you want to look for a text value “shirt” in the Product Name column. ewqorsrhvweqzhxhsohhsexpghammnelxiwxcylzkqzgnaksxrnwtdgkeifwxauofrtzegfsr