If cell contains partial text from list then return value formula. Insert the following formula in cell D5 and press Enter.

If cell contains partial text from list then return value formula 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. Apr 14, 2025 · Learn how to check if a cell contains any text using Excel formulas like IF, ISTEXT, LEN, and COUNTIF. For instance, we have a large array of values and we need to extract all the values matching the string. Mar 14, 2023 · The tutorial shows how to make Excel IF statement with wildcard text and get it to work for partial matches. May 23, 2023 · The tutorial explains the syntax and basic uses of the IF function in Excel. So looks in the 1st column, searches a list (3rd column) and returns the category from the 4th to the second column. Contains a function that you can use to test whether a sub-string is in the string or not. And if you’re not using it to automate your tasks smartly – you’re not doing it right. The IF function doesn’t work with wildcards. Ordinal: Used to perform a case-sensitive ordinal In this article, we will learn about how to extract all partial matches in an array of values in 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. This formula seems to work for two variables but I Aug 17, 2025 · To sum values when a cell contains specific text in Excel, use the SUMIF function with wildcards. Use the following formula in E5 to check whether the cell D5 contains the specific text Passed. For example, Let's say cell a1 contains a really long text-value: A1 ="If horse-racing is the sport of kings then surely bowling is a… very good sport as well" Guide to VLOOKUP Partial Match. Nov 10, 2020 · Following is the Excel formula to find If Cell Contains Specific Text Then Return Value. You want to lookup the price of that product from a table. Also Read: Using =VLOOKUP () Function for Partial Match in Excel Mar 14, 2023 · The tutorial shows how to count the number of cells with specific text in Excel. Partial Match VLOOKUP (Text Contains) By using the asterisk “wildcard” (*), within a VLOOKUP, we can lookup values that contain (partial match) certain text, instead of values that match the lookup text exactly. Nov 21, 2025 · You can perform Excel IF contains partial text searches by combining the IF function with other functions. 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 9, 2023 · Do you need to match part of a string in your spreadsheets? Learn how to check for partial matches in Excel in this guide. Oct 23, 2023 · The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. Nov 10, 2020 · This excel formula will check for partial text in Excel cells and returns what you need! Check if cell contains partial text or part of text. Sep 27, 2024 · Master Excel formula for partial matches using VLOOKUP, XLOOKUP, Filter and more Examples. 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. Feb 13, 2023 · To filter data to include data based on a "contains specific text" logic, you can use the FILTER function with help from the ISNUMBER function and SEARCH function. Apr 19, 2024 · Learn 5 super easy methods to check if a cell contains partial text in Excel. Master flexible conditional logic Nov 20, 2022 · Then, the IF function will return a TRUE or FALSE value depending on whether the partial text was found. Aug 31, 2017 · Now I want the value of Cell 3 to align with Cell 1 through a partial match with cell 2. 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. 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. . 0001. See below for a case-sensitive Mar 22, 2018 · In Excel you may need to lookup just part of the text in a cell. This example used IF and COUNTIF functions to check for a value in a Cell and return required value. Jun 3, 2021 · To retrieve information from a table based on a partial match, you can use the VLOOKUP function in exact match mode with a wildcard. If multiple values match then all matching values in the list are displayed. If the condition is met, it returns a TRUE value, otherwise it returns a FALSE value. Oct 28, 2023 · For example, the formula “If cell contains text then return value” can be used to quickly identify and return specific values from a series of cells. Press Enter, and then the formula returns the corresponding value from the second column; the result is $6100. Jul 28, 2018 · Amazingly, it only works based on the order of your sample data. Nov 28, 2018 · Easily sum cells containing specific text for a partial match in Excel using the SUMIF or SUMIFS function with a wildcard. Jan 27, 2022 · 0 I am trying to get "Okay" if any cell in my sheet Information $46:$46 matches text that is listed on another sheet in Lists D6:D17. This simple yet effective technique will revolutionize your data analysis and reporting, saving you time and effort. Output: Partial match with VLOOKUP for “Dav” Another example Suppose we want to retrieve the details for the last name starting with “Mil”. 55” (note the space after “Gr. 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. Ex: if cell B2 partially contains “Happy” then return the value from the list with the word Happy in it. Either of the following formulas would return a check mark ( ). Jun 3, 2024 · Getting IF Function to Work for Partial Text Match In the dataset below, we want to write an Excel formula to find out if a cell contains specific text. 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. Our formula will search the column A text for the text sequence “AT” and if found display “AT” in column B. We will use a combination of IF function, ISNUMBER function & SEARCH function. The formula in F5 is: = XLOOKUP ("*" & E5 & "*", code, quantity,"no match",2) // returns 50 lookup_value - E5 Apr 5, 2023 · To use select records from a set of data based on a partial match, you can use the FILTER function with the SEARCH function. A common requirement when working with data sets is to evaluate whether a cell contains text and, based on the Beautiful Relaxing Music 🍁 Soothing Autumn Music For Stress Relief, Deep Relaxtion, Calm Nervous How to Return Value in Excel If Cell Contains Text from List Nov 21, 2025 · Searching a massive spreadsheet for specific text can be annoying when you do it manually. Jul 31, 2023 · This tutorial explains how to use an IF function with text values in Google Sheets, including several examples. By combining VLOOKUP with functions like SEARCH, LEFT, and TEXT, you can handle partial matches effectively. Formula examples for exact match, partial match and filtered cells. How can I write this formula for this to happen? Mar 22, 2013 · I need to find a partial text in a specific range and get a value which is X rows under cell index of found text. This approach can save you countless hours by using a formula to check if a cell contains specific text from a list. In the example shown, the formula in H7 is: =VLOOKUP(value&"*",data,2,FALSE) where value (H4) and data (B5:E104) are named ranges. In the example shown, the formula in F5 is: =FILTER(B5:D14,ISNUMBER(SEARCH("rd",B5:B14)),"No results") Which retrieves data where the street column contains "rd". In the example shown, the formula in G7 is: =FILTER (Table1,ISNUMBER (SEARCH (H4,Table1 [Last]))* (H4""),"No results") where Table1 is an Excel Table that contains 100 rows of data in B5:E104. Discover simple techniques to find partial matches and enhance your data analysis efficiency today. Alternatively, you can reference a single cell, lock cell references, and copy the formula down. Apr 30, 2024 · 5 suitable methods to lookup partial text match in Excel. Apart from this function, we can also use the IF statement to test for a substring. Feb 8, 2023 · The VLOOKUP Function lookups a value in the leftmost column of a range and returns a corresponding value from another column. Jun 11, 2021 · Excel formula. And we need to check which are the names with the female suffix. Otherwise, it returns FALSE. Entering the VLOOKUP formula for first name To complete the other details, enter the formula in cells H6 and H7 as shown below: Figure 6. Index/Match - Return value if partial text from cell contained in separate column however, I forgot to mention that I need the formula to search all throughout column A. However, you can use a clever combination of a few formulas to automate the process. If it finds the text “CA”, then it would return the value as “1” (as the text CA is positioned at the very beginning of each of the search string company codes). Syntax for IF CONTAINS in Google 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. May 9, 2024 · 9 ways to combine excel INDEX-MATCH if cell contains text. This is the actual dummy data from the Google Sheet we’ll be using to build out the formula for method #1: 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. Download our Excel workbook, modify data & exercise! 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. One of the major uses of Excel is to conduct efficient searches. Excel file is freely available to download for you. Nov 8, 2024 · Explanation: Concatenate the lookup value with an asterisk (*). ALDI and LIDL return GROCERIES because the LOOKUP function is returning a 1 - but that '1' is from the position of the partial match in the string, not the position of the array returned by the SEARCH function. When we use COUNTIF with partial matching, it counts the number of cells containing a specific text string regardless of its position in the cell. Jul 2, 2024 · In this article, you will get the easiest ways to check if a value is in list in Excel. Lookup text contains using XLOOKUP & SEARCH There is a further alternative to lookups for “text contains” or partial match cases. Note the SEARCH function is not case-sensitive. This can be extremely useful when analyzing data or creating reports. This is an array formula and must be entered with Control + Shift 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. So, in this section, we will try to calculate the total price of the products under the Wafer category. Sep 16, 2025 · Detects whether text contains the value substring. I have a list of names i'm referencing to pull from, and if it has the full name I figured out a formula. SUM IF Cell Contains Partial Text using Case-Sensitive If you want to sum values based on a case-sensitive search, use an array formula combining FIND, IF, and SUM. a substring), you can use the SEARCH function together with the ISNUMBER function. Dec 16, 2022 · To sum if cells contain specific text, you can use the SUMIFS or SUMIF function with a wildcard. Dec 24, 2024 · Uncover the power of conditional formatting in Excel! Learn how to create dynamic formulas that return values based on partial text matches in cells. Aug 24, 2025 · Learn how to check for partial text in Google Sheets using SEARCH, REGEXMATCH, VLOOKUP, and more to easily match and count text. Like searching for a specific text or just text in Excel and returning another value elsewhere 🧐 This process (if successfully worked Jan 26, 2022 · In this example, if cell A1 contains any of the text values in the range E2:E8 then it will return a Yes, otherwise it will return a No. I used: =IF(ISNUMBER(SEARCH(C:C,K3)),N3), and tried VLOOKUP variations too but without success. Although the cell contains “New”, the formula returned “It’s another State”. Feb 12, 2025 · To check if a cell contains specific text (i. Mar 9, 2018 · How to Do a Partial Match in IF Function in Google Sheets Here is a standard IF formula that checks the value in cell A1 and returns a tick mark if the value matches the IF condition. The following built-in comparers are available in the formula language: Comparer. Find out how to use wildcard characters to aid the search. May 13, 2025 · Learn how to use a powerful LAMBDA function, TEXTMATCH, to check if a cell contains specific text (exact match or partial). Aug 19, 2025 · In this article, we’ll cover multiple methods to perform partial text lookups in Excel, including VLOOKUP, combination of INDEX & MATCH, and IF function. Steps: Select cell C15. Otherwise, it will return "False. The formula searches the lookup value in range C3:D8. I'm trying to build a budget sheet that recognises partial text in a column and returns a category. e. Includes downloadable file! 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. The value in cell A1 for this test is “Gr. For example, if you have a cell that contains a transaction description and within that description there is a product name. The formula I'm using is below =IFERROR (INDEX (list,MATCH (1,COUNTIF (longdescription"*"&list&"*"),0)),"") The issue is, if the long description only has their first/ last name or a shortened version of their name it's not pulling it If you organize your data differently, you could use the same formula for each column instead of a different formula per cell, as long as you reference the input absolutely. Dec 24, 2023 · Explore 5 effective methods to vlookup partial match to return multiple values in Excel and apply them in data analysis scenarios. One of its key strengths lies in how it allows users to perform logical tests, manipulate data, and automate tasks via formulas and functions. Here we learn how partial match in VLOOKUP() works, along with examples and a downloadable excel template. Count Cells that contain specific text : Count number of cells if cell contains given text using one formula in Excel. To use this method, we can follow the step-by-step process below. Jul 22, 2013 · Hi! I'm trying to find if a cell contains a partial text match to a list of text values in a range of cells. for excel if cell contains text then add text in another cell. " Apr 16, 2024 · Here, i have described the methods using IF, ISTEXT, COUNTIF, VLOOKUP, etc. Jan 30, 2025 · The image above demonstrates a formula that checks if a cell contains a value in the list and then returns that value. In this example, cell A1 will return a Yes if the string abc appears anywhere in the cell, and a No otherwise. Jul 3, 2024 · Method 4 – Incorporating the XLOOKUP Function to Perform a Partial Match In the first table, the partial match strings are given with rank. The most appropriate choices are using the SEARCH function and COUNTIF. Feb 8, 2023 · The formula will “spill” to perform the calculations on the entire array of values. Find partial text in column and return cell value from row Ask Question Asked 4 years, 5 months ago Modified 1 year, 2 months ago Jul 4, 2024 · 2 suitable uses of VLOOKUP function if a cell contains a word within text in Excel. My Formula works if the cells in Information only contain that value that is mentioned, the problem I now have is that the cells in Information sometimes contain multiple values Jun 12, 2024 · Method 1 – Using VLOOKUP with Wildcard for Partial/Exact Match from a Column Steps: Enter the following formula in cell C15 and press the Enter key. The formula I am using seems to work for the top cell only. 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. Insert the following formula in cell D5 and press Enter. 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. Download our practice workbook, modify data, find new outputs and exercise! Aug 29, 2024 · If Cell Contains Text Then Return Value in Another Cell Excel is a very smart spreadsheet software. The COUNTIF function in Excel counts the number of cells within a range that meet the given condition. Note that SUMIFS is not case-sensitive. Select C12 and enter the formula: hi! trying to return the name from a long description. However, see below for a case-sensitive option. Here, G4 and G5. If you re-arrange the values in column A, it stops working. 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. Apr 5, 2023 · Here, the ROW function is used to generate an array of relative row numbers, and ISNUMBER and SEARCH are used together to match the search string against values in the data, which generates an array of TRUE and FALSE values. ”). Click here to Download the Example File. Dec 14, 2023 · To test for cells that contain specific text, you can use a formula based on the IF function combined with the SEARCH and ISNUMBER functions. We can use COUNTIF and OR function to check the array of values in a Cell and return the given Value. This article serves as a guide to help you use some formulas to search a cell to see whether it contains a specified value. Jan 26, 2022 · This tutorial explains how to check if a cell contains partial text in Excel, including an example. Let’s look at three possibilities: When the product name is just randomly placed within the lookup text: “Sold WHEEL to John’s Motors Ltd Apr 19, 2024 · Method 1 – Using SUMIF Function to Sum If Cell Contains a Text in Excel In the spreadsheet, we have a product price list with categories. 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. IF function is used for logic_test and returns value on the Aug 14, 2024 · The value_if_true argument will return the partial match and the value_if_false argument will return “It’s another State”. We will check whether a cell contains Passed and add a remark in a seocnd Remarks column. Aug 3, 2024 · Method 1 – Using the Excel INDEX-MATCH Formula with Multiple Criteria for a Partial Text The dataset contains the First Name, Last Name, and the Department of employees. This formula allows you to look for partial text matches inside a cell. Download the workbook and practice yourself. Apr 6, 2019 · Here is the Excel Formula to check if cell contains text from list then return value. 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. This method uses the VLOOKUP function with wildcard characters to find and return values that are related to partial text matches. As in example: loo May 12, 2017 · I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell. Jun 11, 2024 · This article will illustrate the different 9 ways for conditional formatting partial text match in Excel effectively. In the example shown, the formula in C5 is: =IF(ISNUMBER(SEARCH("abc",B5)),B5,"") To test for "if cell equals" you can use a simpler formula. 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. 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. It will return "True" if it found the character or string. Includes examples, VBA code, and a downloadable file. Below table summarizes the formula and VLOOKUP results Mar 16, 2020 · Thus, the excel would search for the text “CA” in the company code list and return its position. Nov 13, 2023 · Your learning today comprises checking cells for partial text using the SEARCH, COUNTIF, and MATCH functions along with other functions to control the outcome. In this article, I will show you a couple of methods to check if a cell contains partial text or not using some simple formulas. I have used the following formula for that: =IF(OR( 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. The following example shows how to use this formula in practice. Put the following formula inside the cell: In Power Query, you can use the Text. In case you'd like to look for a partial match in the IF formula (Excel IF function with text containing another word), you can't use an asterisk sign just like you would in the SUMIFS or COUNTIFS Master Excel with 'If Cell Contains' formulas! Learn step-by-step tips to simplify your data analysis and automate tasks effortlessly. You’ll get the correct result To check if a cell includes a particular piece of partial text, use the Excel formula below: =IF(COUNTIF(A1,*abc*),Yes,No). CONTAINS Function Is used within another function to search for a character or string. To return a result based on partial text: STEPS: Create cells to insert the partial texts and display the Search Result. I will also show you how to highlight cells that contain the partial text string. Download our Excel workbook, modify data & find new results with formulas. This function doesn't support wildcards or regular expressions. In the example shown, the formula in cell F5 is: =SUMIFS(C5:C16,B5:B16,"*hoodie*") This formula sums the quantity in column C when the text in column B contains "hoodie". If there is a match, then it returns the respective value that it found, whichever row it was in, within Sep 28, 2022 · For the If formula in bold I want to return the word "COMM" to the Milestone Group column if the text "MTG" or "EMAIL" is found anywhere in a cell in the Task Name column. For example if cell with text “Amazon*F7H76” contain “Amazon”, then return “Amazon” and do on 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. Returns true if the value is found. We need to identify the names in the second table that contain the partial match strings and then return the rank associated with those names. In cell F5, apply the formula. There isn’t an exact IF CONTAINS Google Sheets function. I have tried with INDEX and MATCH functions but without success. Wildcard characters help you define the partial text, then COUNTIF checks for partial value, and IF returns the result according to that. Nov 18, 2024 · Mastering how to VLOOKUP part of a cell text in Excel opens up new possibilities for managing and analyzing data. In the below example, we have a list of names with the suffix for male and female. Dec 14, 2022 · The current formula is incredible, but works on a row by row basis. Let's get some understanding of these functions. The functions we will use are TEXTJOIN, FILTER, ISNUMBER, and SEARCH. 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. Feb 20, 2025 · Learn how to perform VLOOKUP in Excel with contains text. The optional argument comparer can be used to specify case-insensitive or culture and locale-aware comparisons. xecbusk izpkv nia oglqxdp mudxkly tonfqd dyqmdnm otzo bjspa rpam tvbnyxjx espsq knjf fqgpgx bmilghzo