If cell equals one of multiple values Most users will think first of the IF function. In the example shown, the formula in C9 is: {=AND(B5:D12=F5:H12)} Note: this is an array formula and must be entered with control + shift + enter. In the example shown, the formula in I5 is: =SUMPRODUCT (SUMIFS (E5:E16,B5:B16,things)) where things is the named range G5:G7. In Excel, the IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is True or False. In this article, you’ll learn how to apply conditional formatting rules in Excel where formatting depends on multiple possible values in a different cell. Download our Excel workbook, modify data, find new outputs and exercise with formulas! Aug 15, 2023 · If one of multiple cells contain specific text then return value I am stumped on work project. Jul 3, 2024 · How to use the IF function of Excel with range of values. Sep 16, 2025 · Learn more about: Value. The result is $19. This means we can't use IF by itself to test for a substring like "abc" or "xyz" that might appear anywhere in a cell. Equals( value1 as any, value2 as any, optional precision as nullable number ) as logical Oct 15, 2022 · And one of the things we can do in Excel is to check whether two cells match or multiple cells are equal. Conditionally formatting cells if their value equals any value of another column Asked 10 years, 11 months ago Modified 5 years, 11 months ago Viewed 141k times Jun 6, 2014 · Re: if cell value is equal to multiple text values If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED. Oct 22, 2024 · Learn how to check if a cell equals any value in a list in Excel using formulas, simplifying data validation and comparison. One option (seen in the example) is to create a logical test with Mar 12, 2021 · I want to format cells $C$2:$C$32 if their value (text) is equal to not one other specific cell but one out of multiple cells, let's say $C$35:$C$42. Jan 7, 2018 · To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. This returns TRUE which I understand is how the OR function works. May 22, 2022 · 1. Sep 8, 2023 · But if i want to test against instances of a list of multiple values instead, eg cell A1 is any of {"Apples","Oranges","Bananas"} that would return a single TRUE value if there is at least one match. The value in the Result column is the outcome of the IF formula The logical test checks to see whether the cell in the Day column (B5) = "Wednesday" (we use the speech marks to tell Excel the value we're performing the test on is text rather than a number) If the value in the Day cell is "Wednesday", then the result will be Yes. But what if you want to apply conditional formatting to a cell based on the value in some other cell? Aug 18, 2025 · This becomes more interesting when you want to apply formatting based on multiple values in that other cell. Is there a way that allows us to highlight cells that equal to more than just one value? Example In this article, we will be using this table as an example. a cell can be "this" or "that"), then you can build a more compact formula using an array constant. 6 days ago · Here, we’ll look into the values of Column B and highlight the cells in Column A if the value in Column B is equal to, greater than, or less than $500. By Using the IF Function If you want to get a cell value if one cell equals another then return another cell in Excel, then using the IF function would be the very simple and easy way to do that. May 23, 2023 · The tutorial explains the syntax and basic uses of the IF function in Excel. =IF (Something is True, then do something, otherwise do something else) But what if you need to test multiple conditions, where let’s say all conditions need to be True or False (AND), or only one condition How to make the OR function more efficient when 1 cell can equal multiple values instead of listing a new logical for each instance? Is there a way to shorten an OR function like below? I feel like it's inefficient to listen a whole new logical in order to make the function work. There are situations in Excel where you would want to count values in one column based on criteria in another column or columns. When all cells are No, I just need F13 to stay blank. This is an array formula and must be entered with Control + Shift 1 Using conditional formatting to format cells with color works fine if I want to do a single value, but now I'm in a situation where I'm looking at 40 different values that appear multiple times in a pretty big spreadsheet. I'm just at a loss to how I can incorporate multiple outputs into a single cell given multiple potential conditions. Cell B4 contains text ZDS found in Jan 16, 2023 · This tutorial explains how to apply conditional formatting to cells in Excel based on multiple text values, including an example. Aug 11, 2011 · Hello All, I am trying to have a formula that will check whatever is in a cell, if it is one multiple criteria, then I want it to return something else each time it appears. So if I have 5 cells: Dog Barks Cat Purrs Mouse Squeaks Cat Purrs Dog Barks The left column contains one of several Oct 19, 2018 · Using Excel, I would like to conditionally highlight a cell, if the value of the cell itself is not one of several values. I have a cell F13 intended to be dependent on a range of 4 other cells. Nov 15, 2022 · Hello @JorgePinho , I want to add a custom column in Power query with one same condition but equals to multiple values, for example (IF you="boy", then "Jimmy" or "Alex" or "John") Hope my explication is better. Jul 3, 2024 · 6 Ways to SUM with the IF Condition in Excel We will be using a sample product price list as a dataset to demonstrate all the methods. I tried creating a formula that shows “TRUE” if all these cells have the value either yes or n/a, else to show “false” but I can’t seem to figure it out. Examples below where A1 can equal the values 1, 3, 6, or 7. Excel allows a user to check if a cell is equal to one or many things from a range, using the SUMPRODUCT function. Is there a way I could enter all the values into the field rather than repeating the operation 40 times? Apr 5, 2021 · We all know that we can highlight cells that equal to specific value by conditional formatting. Case study: Let’s say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things in range E5:E9. IF Cell Equals Find out how to use the Excel IF statement to determine whether a cell equals a certain value with this guide. If A1 or B1 contain "text value 1" return C1. I have all the values in the same column "Type" but the output should be different based on the values as mentioned below. Expected result and method: Write a formula that can identify one or more values in a single cell and correctly “code” those values with either a 1 or a 0 in the subsequent columns. Learn how to determine IF a cell equals a specific value. How to check cell equals one of many things in Excel If you want to test a cell to see if it equals one of several things, you can do so with a formula that uses the SUMPRODUCT function. Right now I have this: = May 21, 2024 · The goal is to do something if a cell contains one substring or another. See full list on exceljet. Use IF function with any range of values accompanied by the familiar functions . For example, cell B3 contains XBF which is found in cell E7. 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. Aug 29, 2024 · This is no longer a thought only – in fact, the IF-THEN formula in Excel lets you do this in a snap. Go to the Home tab and click the Conditional Formatting drop-down in the Styles group. Furthermore, we may need to check if two cells or multiple cells match or is equal before performing a formula or calculation. Here’s how to implement it for cells A1 to C1: For checking, if numeric cells (such as numeric values, percentages or dates) have the same value, you can use a combination of AVERAGE and MAX. May 31, 2023 · This tutorial explains how to check if a cell contains one of several values in Excel, including an example. The result of the SUMPRODUCT function Syntax of the SUMPRODUCT Formula The generic formula for the Jun 21, 2024 · You can count one column based on criteria in another column by using COUNTIF, COUNTIFS, SUMPRODUCT Functions, Pivot Table, and VBA code. Jun 3, 2002 · How do i write an IF macro statement so that compares a value to a range of values contained in a range of cells in a worksheet. 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. e. May 14, 2022 · To count the number of cells equal to one of many values, you can use the COUNTIF function inside of the SUMPRODUCT function. Mar 28, 2023 · Excel has a number of built-in Conditional Formatting rules that can be used to format cells based on the value of each individual cell. In the generic form of the formula above, range is the data, and things represents the values to count. May 20, 2025 · Checking if cell contains one (or multiple) of several exact values in Excel Asked 5 months ago Modified 5 months ago Viewed 121 times Jan 20, 2025 · How it Works If all numeric values in a range are the same, the average will equal the maximum (and the minimum). Jun 18, 2021 · I got another challenge due to multiple different output text within the same formula. As we already know, IF is one of the most basic functions in Excel, and it is mainly used to make a logical comparison between the two numbers. net Mar 22, 2023 · In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i. May 24, 2024 · 10 easy methods to compare two cells in Excel. Except EXACT function rest of the methods are case-insensitive. However, one problem with IF is that it does not support wildcards like "?" and "*". EqualsValue. As a result, we get True or False. Jun 8, 2020 · Thank you! 👍👍 This Excel video tutorial demonstrates how to check whether a cell contains one of many things or values. Could you please help one more time how I can extend the formula to meet the below conditions? Nov 16, 2021 · I'm trying to figure out how to perform this calculation. However, if that cell has one of four different values, I want a different cell to display certain text. The cell should be contain of the following percentages – 0%, 6%, 12% - o Aug 29, 2024 · Step 4) Write the value that you want Excel to return if the cell contains a text value as the value_if_true (the second argument). Select the cells you want to highlight based on another column. 5))) in which I7 is the cell containing qty. Oct 15, 2014 · If two cells match, return value from third Asked 11 years, 1 month ago Modified 6 years, 9 months ago Viewed 633k times May 27, 2015 · Conditional formatting if a cell does not equal one of three values [closed] Asked 10 years, 5 months ago Modified 10 years, 5 months ago Viewed 78k times Conditional formatting in Excel allows you to format cells based on the values in the cells. Mar 13, 2023 · How to construct the If match formula in Excel to see if two or more cells are equal and return logical values, custom text or a value from another cell. So far this works. Range F5, F7, F9 & F11 will either be "No" or Not Effective". 99)),(IF(OR(I7>1,I7<4),49. Additionally blank cells should remain untouched. Aside from simply looking at the values, we can use different functions to compare the values accurately. Want to highlight rows based on a cell value? In this tutorial, I will show you how to use Conditional Formatting to highlight rows in different scenarios. We selected the range A2:A10. In the example shown, the formula in cell F5 is: =SUMPRODUCT(--(rng=B5:D5))>0 where "rng" is the named range H4:H10 and contains the values to look for. Sep 10, 2015 · Likewise, using your knowledge of the range of values that will be encountered, the clunkiness of the leading delimiter in the first string and concatenating the delimiter to the front of the search value, could be removed. This step by step tutorial will assist all levels of Excel users in checking if a cell is equal to one or many values from another range. From the menu, select New Rule. Sep 30, 2024 · Discover how to sum values in Excel equal to one of multiple values, enabling advanced data filtering and analysis using SUMIF for complex criteria. Nov 19, 2013 · Hi all: I'm working on a spreadsheet in which I would like the cell to display "T" if referenced cell is equal to 11, 12, 14, 15, 18, 19, 60 or 61 / "B" if referenced Dec 21, 2022 · To sum values when cells are equal to one of many things, you can use a formula based on the SUMIFS function and the SUMPRODUCT function. 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. 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. The built-in formatting settings and Conditional Formatting are made to format cells based on the value in the cell itself. Could you please help one more time how I can extend the formula to meet the below conditions? Dec 20, 2018 · Return a value if another cell has one of many values Ask Question Asked 6 years, 11 months ago Modified 6 years, 10 months ago Jan 30, 2018 · However it only marks a 1 if the last one has an x in it, I want it to check if whether those cells all have x then it should have a 1, otherwise it would be a 0. 50, the sum of Price where Item is "Apples", "Pears", or "Kiwis". This tutorial shows how to check if a cell contains at least one of many items and return a value using an Excel formula, with the SUMPRODUCT function. Oct 25, 2021 · The sample file attached demonstrates this. 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. Apr 8, 2024 · If one cell equals "Value", then the cell the formula is in equals "figure", for multiple value options Outlook12 Apr 8, 2024 O Sep 23, 2015 · The furthest I've gotten is: =OR((IF(I7<2,49. Some of the ways of counting values in one column based on criteria in another column (s) are: Employ PivotTable Apply Jan 30, 2025 · The image above shows an array formula in cell C3 that checks if cell B3 contains at least one of the values in List (E3:E7), it returns "Yes" if any of the values are found in column B and returns nothing if the cell contains none of the values. Apr 23, 2024 · Here we discussed 4 methods to check if multiple cells are equal in Excel. Jul 1, 2024 · The problem goes as this: I have 5 cells that will have one of three values: Yes, No and N/A. Highlight Cells Rules Perhaps the most straightforward set of built-in rules simply highlights cells containing values or text that meet criteria you define. Apr 27, 2024 · We use AND, OR, ISNUMBER, SEARCH, and SUMIF functions as well as their combination to conditional formatting multiple text values in Excel. Jan 4, 2021 · To test if one of several values exists in a range of cells, you can use a formula based on the SUMPRODUCT function. Figure 1. I have a cell in a spreadsheet that can have virtually any value. For example, you may want to find out from your dataset how many manufacturers of different products are based in certain regions of the country. If the value is equal to any of the values in the range of cells, then it returns true. 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. . This piece gives an explanation of the formula as well as a detailed example and illustrations to follow. ceg zqqzij sfpn jvy bnik agt nulx hrv rewtg hft rezuoi wjkat uoej cbcqjl bas