Comments on: Excel: If cell contains formula examples

One of the most common tasks in Excel is checking whether a cell contains a value of interest. What kind of value can that be? Just any text or number, specific text, or any value at all (not empty cell). Continue reading →

Comments page 2. Total comments: 198

  1. Hi! I am attempting to replace the contents of a cell with a number based on whether or not that same number is found in within the cell. Here is the formula I've attempted but I cannot figure out why it is consistently returning a "0" rather than the corresponding number:

    =IF(C29(C29, "*1*"), 1, IF(COUNTIF(C29, "*2*"), C29, IF(COUNTIF(C29, "*3*"), 3, IF(COUNTIF(C29, "*4*"), 4, IF(COUNTIF(C29, "*5*"), 5, "")))))

    Any help would be greatly appreciated!
    Lacy

    1. Hi! Your formula returns nothing because it contains an error. The formula may look like this if you use the COUNTIF function in all conditions:

      =IF(COUNTIF(C29, "*1*"), 1, IF(COUNTIF(C29, "*2*"), C29, IF(COUNTIF(C29, "*3*"), 3, IF(COUNTIF(C29, "*4*"), 4, IF(COUNTIF(C29, "*5*"), 5, "")))))

      This formula works only with text values. If C29 contains a number, the function returns 0.
      It is also unclear what the formula should return if the text contains multiple numbers. For example, "1234".
      To offer you a formula, explain your task in more detail.

  2. Hi I have a table where a column captures days overdue (Table2[Ontime / Overdue])
    In the same sheet I have a summary at the top showing [P1 etc is the cell it is in]:

    [P1] Overdue by 1 Days
    [P2] Overdue by 2 Days
    [P3] Overdue by 3 Days
    [P4] Overdue by 4 Days
    [P5] Overdue by 5 Days
    [P6] Overdue by >5 Days

    (formula in Q1 etc follows =COUNTIF(Table2[Ontime / Overdue],P1) where P1 is a cell with text "Overdue by 1 Days". It is text and number

    I am specifically looking to have the [Q6] "Overdue by >5 days" return how many cells have the text showing anything greater than 5 days, though I am having trouble having the formula calculate where it searches the cells (Table2[Ontime / Overdue] for where a number greater than 5 is present.

    Any help is much appreciated.

    1. Hi! If I understand your task correctly, you cannot conditionally count text strings that contain different numbers greater than 5. You can only compare to a number, not text.

      1. Thank you for your response, that is good to know. I found a workaround by using another column to use standard count for (Table2[Ontime / Overdue]), then counting how many times that column had instances >5 :)

  3. Hello - I am sure an easy one but using the formula "=IF(AY11"", "1", "")" ... The resulting retunr of 1, even when formated to number isn't pivotable and is unable to sum the qty of one's on the said Pivot report.

    Any ideas anyone would be greatly appreciated.

    1. Hi! Sorry, I have no idea exactly what the task is.
      It's hard to tell exactly what you're asking for as it's currently written.

  4. Hello!

    I have a question about VLOOPUP(lookup_value, table_array, col_index_num, [range_lookup]). Can we use an array in the "lookup_value"? Something like {"string1";"string2";"string3"}. I just have a long list of complex names, which are sometimes different, but mean the same thing. And in order to match them to the right IDs I need a complex search not out of one word, but multiple. How can I do it? Maybe not VLOOKUP, but something else?

    1. Hi! If you use {"string1"; "string2"; "string3"} as lookup_value, you will get an array of three search results. This formula will return TRUE if at least one value is found:

      =SUM(--NOT(ISNA(VLOOKUP({"aaa";"bbb"},B2:C30,2,FALSE))))>0

      For more information, read: ISNA function with VLOOKUP.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      1. Thanks, but it looks like I phrased the question poorly. I ment if it is possible to have {"string1";"string2";"string3"} as one of the options in the table_array. So that VLOOKUP would have different variations of one name for each ID. Because now when I put a certain name as lookup_value and then put in one cell of the table_array something like {"1_variation";"2_variation" (correct one);"3_variation"}, it gives me N/A, instead of ID, whcih I was looking for. And if I put TRUE as [range_lookup], then sometimes it gives the wrong answer because of alphabetic structure. Sorry for the inconviniece.

        1. Hi! You can write all the variations in one cell as a text string. For example, "1_variation 2_variation 3_variation". Use the TEXTSPLIT function to get an array of variations from this text string. The formula might look something like this:

          =SUM(--NOT(ISNA(VLOOKUP(TEXTSPLIT(H1," "),B2:C30,2,FALSE))))>0

          I hope it’ll be helpful.

          1. Thank you again! Maybe I am getting something wrong, but your formula has TEXTSPLIT in lookup_value, but variations are inside of the values of table_array. I have multiple versions of one name for each ID in order to cover different spelling and those variations are in the table_array, together with their respective IDs (B2:C30 in your formula). It looks like {"1_variation";"2_variation";"3_variation"} | ID. Lookup_value is just a name that should have an ID from the B2:C30, depending on spelling. Also, your formula returns Bool, but I need ID itself. Is there a way to do it?

            1. Hi! Each of your new questions is different from the previous question. To give you an accurate answer, give an example of the source data and the result you want to get. Describe the problem accurately and in detail.

              1. Hi! I said "I ment if it is possible to have {"string1";"string2";"string3"} as one of the options in the table_array" and "but your formula has TEXTSPLIT in lookup_value, but variations are inside of the values of table_array", so I did't really say anyting different, but if it still is not clear for you, then I will give this example:

                | | | |
                №| A | B | C | D |
                -----------------------------------------------------------------------------------------------------------------------------------------
                | | | |
                | | | |
                1 | 2_var_for_tag1 | VLOOKUP(A1,C1:D10,2, true) -> tag1 | {"1_var_for_tag1";"2_var_for_tag1";"3_var_for_tag1"} | tag1 |
                | | | |
                2 | 3_var_for_tag2 | VLOOKUP(A1,C1:D10,2, true) -> tag2 | {"1_var_for_tag2";"2_var_for_tag2";"3_var_for_tag2"} | tag2 |
                | | | |
                3 | 1_var_for_tag3 | VLOOKUP(A1,C1:D10,2, true) -> tag3 | {"1_var_for_tag3";"2_var_for_tag3";"3_var_for_tag3"} | tag3 |
                | | | |
                ...

                And so on! Simply adding a new row for each variation won't work, because I have too many rows with data. Hope it will help!

  5. Hi!

    I have a fairly specific case and any help would be greatly appreciated. There is a list of signal names that may be slightly different but mean the same thing, for example in column A I have:

    Tempreture in the boiler
    T in the boiler
    Pressure in the tube
    Tube pressure
    P in the tube

    And for each signal there is a list of tags that are always the same, for example they are in column F:

    TmBl
    PrTb

    I need to map different versions of the same signal to its tag in the next column, including the possibility of different spellings. So you should end up with something like this:

    Tempreture in the boiler TmBl
    T in the boiler TmBl
    Pressure in the tube PrTb
    Tube pressure PrTb
    P in the tube PrTb

    I know that I can do this with =IF(COUNTIF(A1;"*Signal1*");"Tag1";IF(COUNTIF(...) ) , but this approach is too long and messy in case I have 50 signals and 150 variations of them. Is there another convenient solution? Maybe I could have some kind of dictionary with keywords for each signal or something like that?

    Thank You in advance!

      1. Thank you for the reply, but I am not sure if you can use VLOOKUP or INDEX MATCH with the multiple keywords search. For example, I might have something like:

        T after the boiler
        Temperature after the boiler
        T before the boiler before the valve
        T before the boiler after the valve

        Which means that if my keyword will be only "T", then I will get only the first option. And if I use such keywords as:

        ("T", "after", "boiler")
        ("T", "before", "boiler", "valve")
        ("T", "before", "boiler", "after", "valve")

        Then I will loose the second record where I have "Temprature" instead of "T". So it should be something like:

        ("T", "Temperature", "after", "boiler")
        ("T", "before", "boiler", "valve")
        ("T", "before", "boiler", "after", "valve")

        Which means that the formula should be able to take either "T" or "Temperature" AND other words in order to get it right. Is there a way to do this in Excel?

  6. I am trying to identify the column # of a cell within a range that contains 2 different strings of text (ordered if necessary), but those are not the only strings within the cell.

    e.g. A1 = string 1, string 2, string 3; A2 = string 1, string 4, string 5; A3 = string 3, string 4, string 6

    I would like the formula to look at the range A1:A3 and return the position of the cell with string 1 and string 5. As noted above it can be written so they are ordered or unordered, if one is easier than the other.

    1. Hi! If I understand the question correctly, you can determine the position of the cell that has the text strings "string 1" and "string 5" using the MATCH formula. For example:

      =MATCH(1,ISNUMBER(SEARCH("string 1",A1:A3)) * ISNUMBER(SEARCH("string 5",A1:A3)),0)

      For more information, please read: How to find substring in Excel

  7. I have a sheet with a list of clients and a list of the companies they work for. Is there a way to say if the cell is equal to a company name to then show the employee name on a separate sheet??

  8. let say i have a cell that contain "Monday morning, tuesday morning" but by using the if function who do i only say yes to one of them which is Monday?

  9. Hi, I have a large set of data with numerous columns, I need a formula that will search if any cells in a row contain a certain key word, if so, I want it to return the FULL contents of that cell.

    Example, say I have in row 2, A2 (Star), A3 ($56), A4 (Changed Sales Rep), A5 (3/4/23 updated to cancelled), a6 (Project Name)

    And I want the formula to search for 'Cancelled' and return everything in the cell, in this case it would be cell A5 and the return should be '3/4/23 updated to cancelled'

    I essentially have a ton of terrible data outputted that cannot easily be delimeted and I need to find the date tied to any substring within the cells that contain the word cancelled.

    Thanks in advance.

      1. Thanks, that did it, very much appreciated!

  10. I'm trying to figure out how to get column c cells which contain various number .ie 20227, 208566, I want column d cells to look for a specific number in C and fill D with the value in A i.e shoe, house etc

  11. okay so i have 261 rows of data in columns a through h. if one of 32 names is in column A2:A261 i need to fill in the data in lines b/row to h/row for example

    =IF(A2:A261=A268,B268:H268, IF(A2:A261=A269,B269:H269) ) if i do trhis all the way to =IF(A2:A261,A299,B299:H299) followed by 32 closing brackets will this work to fill my data

  12. Good Morning,

    I have a slightly different request.

    I am trying to write out a sum in one cell, 1+2 for example and in the next cell I want the equation to read =(previous cell) so that it returns the product of the previous equation.

    I know I could put each part in a different cell and write the sum as =(first number cell) + (second umber cell) but the list of sums might contain variations or longer sums such as 1*3*(5/4)+7.

    Many thanks

  13. So I worked out how to make my IF formula work but now I'm facing another issue.

    I have in column Q the ages of some people. I need stats on their age.
    So in the following columns I have age ranges R=13-25, S=26-35, T=36-64, U=65+

    So for the person in Q21 (which is 20) I've got: =IF(AND(Q21>13,Q21<25),"1","0") in cell R21 and in R21 a 1 appears as they're 20 years old. I've made this work for all the people and there are 0's and 1' in the columns to notate their age. Now I want to total how many people are 13-25. My SUM formula isn't working. Is that because It's not reading those cells with the 0's & 1's as numbers because of the IF formula in the cell?

    Another SUM formula is able to add up the 1's as a result of an XLOOKUP formula.

    I hope this made sense...

      1. Thank you! I didn't know having the number written with quotation marks around it gave it a different value than without. It's working now.

  14. Your wild card example says it will return "B" for anything with "banana" in it, but it returns "L" when it says "yellow banana"

    1. Hi Zac,

      You are right - the LOOKUP function does not work correctly with the wildcard character, so I removed that example. Sorry for the confusion.

  15. I have a column with bank statement description in which reference number written at the end of each line and starts with LFT. Is there any why through which i can separate all LFT Number written at the end of each line. Each Line contains different set of long and short description that why i can't use text to column. Let me share two examples of each call .

    Example 1: "Transfer Username AHM CABDUL HADI MOHAMMED HAYTHAM AL-TABB LFT23163L52RF "

    Example 2: "Transfer ABC travel NAEEM HUSSAIN LFT23163VJTC8G47 "

    How can i separate the alphanumeric written at the end and starts with LFT using a formula.

  16. Hi all! Incredible post!

    I have only 1 question, because I've been trying to solve this issue for hours and I cannot get around it...

    I want to be able to do the following:

    If cell A1 contains text "ABC" and cell B1 contains text "DEF", result = "100", but if cell A1 contains text "ABC" and cell B1 contains text "GHI", result = "87"; and also, if cell A1 contains text "ABC" and cell B1 doesn't contain "DEF" nor "GHI", result = ""

    In "words", this means that I want a cell to reflect a certain result depending on the text content of 2 different cells.

    Does anyone have any idea how to do this?

    Thanks a bunch.

    Best regards,

    Charles

  17. If cell contains formula and i want that cell value.
    Ex: C2=G2, i want C2 value.

    1. Hi! To check if a cell has the right match use below formula, if it match's it will give "C2" value else return "No Match" where values not match.
      =IF(C2=G2,C2,"No Match")

  18. Hi,

    I would like to use formula =LOOKUP(A2;{"forecast";"sales"};{"F";"S"}) but unfortunately this formula is not working correctly.

    My data looks like this (cells contains this kind of information):

    JLP ASAF hide sales
    forecast
    new fabric Forecast
    new fabric sales
    new fabric still under Development
    sales
    sales New Fabric
    one time only to then be disc
    only 126m sold in 2022 avg 2m per week
    only 15m sold in 2022 purchased from sales
    only 77m sold in 2022

    Therefore, if cell contains a word „forecast“, when abbreviation should be „F“. And if cell contains a word „sales“, when abbreviation should be „S“. Unfortunately LOOKUP function is not working. Could you please help me solve this issue. Thanks in advance.

      1. I want ISBLANK formula before above formula, like=IF(ISBLANK(D2),"Blank",IFS(ISNUMBER(SEARCH("sales",A1)),"S", ISNUMBER(SEARCH("forecast",A1)),"F")....but its not working.

        First to find A1 is blank or not, if it is not blank then A3 will be the value of above formula, I mean this formula =IFS(ISNUMBER(SEARCH("sales",A1)),"S", ISNUMBER(SEARCH("forecast",A1)),"F")

        can you please help me out

          1. I guess, I couldn't explain correctly.

            Actually I want 2 formula in a cell. 1st =ISBLANK along with =IFS(ISNUMBER(SEARCH(

            First to find A1 is blank or not, if it is not blank then A3 will be the value of | =IFS(ISNUMBER(SEARCH( | formula,

            I mean =IF(ISBLANK(A1),"Blank",IFS(ISNUMBER(SEARCH("sales",A1)),"S", ISNUMBER(SEARCH("forecast",A1)),"F")

      2. Yahooo! Now it's working! Thank you so much!

  19. I have a dataset full of dozens of models. The variables used in each model need to be added to one cell with a (+) added between each variable. Not all variables were used in every model, so I need a way to include only the variables and plus sign IF the cell has text (which denotes the variable was used in that particular model). Example of what I mean is below if it shows up properly on the comment page.

    A B C D E (output)
    Toucan Robin Macaw Toucan + Robin + Macaw
    Ostrich Robin Ostrich + Robin
    Toucan Ostrich Robin Toucan + Ostrich + Robin
    Robin Macaw Robin + Macaw

    Thank you for the help!!!

  20. Hi,

    I'm trying to find the best formula for my scenario,
    I need to search cell B2 - if it contains one word and contains one cell, then result another cell.
    Currently, the below formula works
    =IF(AND(ISNUMBER(SEARCH("H-Type",B2)),ISNUMBER(SEARCH($AW$2,B2))),$AZ$2,"")

    I need to add, if this is false, then continue searching for results with alternative options
    Like (SEARCH("P Type",B2)),ISNUMBER(SEARCH($AW$3,B80))),$AZ$3,"") and continue until result is found

    Sample/search options for B2:B1840 fields - maybe 30 different text options like "H-Type" or "P-Type"
    H-Type® (18"/450mm) X with X and X
    P-Type (90"/2200mm) X with X and X

    Sample options for AW2:AW24 cells
    12"
    18"
    90" etc

    Results - Table(called Sizes) between AZ2:BD24 depending on result of text option
    Example. If "H-Type" then result within AZ:2AZ24, if "P-Type" then result within BB2:BB24 etc
    Actual value of result will be single numbers between 2 - 20

    So, I need to check cell B2 contains "H-Type" and AW2, if yes show value in AZ2, if no check if B2 contains "H-Type" and AW3, if yes show value AZ3, if no continue until a match is found. There should always be a match to one of the combinations between each text and cell option.
    or
    If B2 contains "H-Type" and AW2 then VLOOKUP(B2,Sizes,4,FALSE))
    If B2 contains "P-Type" and AW2 then VLOOKUP(B2,Sizes,6,FALSE))

    I hope that all makes sense,
    I think this will be way to many conditions for one formula, is this formula even possible?

    Thanks,

    1. Hello!
      I recommend using the IFS function instead of the IF function for many alternative searches. For more information, read this article: The new Excel IFS function instead of multiple IF.
      For example, try this formula:

      =IFS(AND(ISNUMBER(SEARCH("H-Type",B2)),ISNUMBER(SEARCH($AW$2,B2))),$AZ$2, AND(ISNUMBER(SEARCH("P Type",B2)),ISNUMBER(SEARCH($AW$3,B80))),$AZ$3)

      I hope my advice will help you solve your task.

  21. I am working on a running document. I am attempting to create a formula that will subtract from two cells IF text or a number is present in a different cell. For example if text or a number is present in V3 or U3, then perform E3-D3. My formula looks like:

    =if(istext(V3,U3),E3-D3)

    But it does not work.

    Any ideas?

  22. Hello, I need a help in "improv"ing a formula.

    I have something like this in an if: IF(OR(C13="Closed",C13="Dropped",C13="Suspended"), ##DoThis##, ##DoThat## )

    Can I rewrite it in a manner where I need to type C13 only once? like..
    IF(SOMEFUNCTION(C13,"Closed","Dropped","Suspended"), ##DoThis##, ##DoThat## )

    Reason being, there may be another string that might have to be checked for C13, and I am too lazy to type in C13 again ! :)

    I am aware that I can probably write it like:
    IF(ISNUMBER(FIND(C13,"Closed"&Dropped"&"Suspended")), ##DoThis##, ##DoThat## )
    But, is this the only option?

    Thank you very much.

    1. Hello!
      Instead of a formula
      OR(C13="Closed",C13="Dropped",C13="Suspended")
      use
      OR(C13={"Closed","Dropped","Suspended"})

  23. Hello,

    I am trying to add this function; where a particular range of numbers results in a specific text response
    for example:

    scores in one column within the following ranges (98) will result the following descriptors in the adjacent column (exceptionally low, below average, low average, average, high average, above average, exceptionally high).

    how do i write out a command?

      1. Excellent!
        Thank you!!

  24. Hi,

    Please help provide the formula if a range of cells contain the letters BR, then I would like the sum of another range of cells of just containing BR to return the value, thank you.

    1. Hello!
      To find the sum of the cells by condition, try using the SUMIF function. I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

      1. Exactly what I needed thank you!

  25. Hi Alexander,

    I'm currently using the below formula to extract/filter out data from a master sheet to another sheet, however this gives blank cells/rows in between which I don't want. I tried adding VLOOKUP to the formula but unsuccessful (still noob at using LOOKUP).

    =IF(ISNUMBER(SEARCH($I$8,Master!H10)),Master!H10,"")

    May I seek your help on how to edit the formula, so that I can have a list of results without blank cells/rows in between please?

    Thanks in advance!

  26. HI! I am wondering if you guys can help.. Doing some report for my team
    Conditions if scores are from 6.42-7.41 = 5 Coins... if 7.42 - 8;49, 10 coins and if 8.50 - 300, 15 coins.. I used

    B22 = 5
    B23 = 10
    B24 = 15

    =IF(C3>=6.42,$B$22,IF(C3>=7.42,$B$23,IF(C3>=8.5,$B$24,)))

    C3 is 25.53 but result still is 5. Should be 15. Not sure if there's a mistake with my formula. Thank you.

  27. If cell G4 contains wording Baseline

    Cell H19 mustn't add 5% (Costing)

    If cell G4 contains wording Project

    Cell H19 must add 5% (Costing)

  28. Please advise a number check formula that will yield "if lesser then" in a string scenario (dimensions in a cell) to find out if any of the numbers in their position are lesser then 5.

    Cell Example: 11 x 3 x 4

    Cell Formula Result: false true true

    1. Hello!
      To extract all numbers from text, use a user-defined function RegExpExtract. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).

      =TEXTJOIN(" ",,(--RegExpExtract(A5,"\d+") < 5))

      To combine results in a single cell with a delimiter, use the TEXTJOIN function.
      You can also extract all numbers from text using Extract text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  29. Hi Dear,

    I have an excel file where a cell can contain 2 languages (Arabic and English). Is there a way to highlight which of these cells in the sheet have 2 languages. Also, is there a way to further determine if the English language (word) starts at the beginning of the sentence.

    The problem is that we are working on a project to create an app, some sentences should include popular english words followed by arabic words. But whenever the sentence start with english, a problem appears in our user interface. That's why I need to determine which sentences in the cell start with and English word.

    Thank you!

    1. Hello!
      You can determine the ANSI code of the first character in a text. The ASCII value of the lowercase alphabet is from 97 to 122. And, the ASCII value of the uppercase alphabet is from 65 to 90. To do this, use the CODE function.

      =CODE(LEFT(A1,1))

      See CODE function example here.
      I hope my advice will help you solve your task.

      1. Thank you so much Alexander. This helped me a lot!

  30. Hi, need help with a formula, please:

    If cell contains certain text, put a value in another cell: So that would be =IF(ISNUMBER(SEARCH("Sub",A2)),"Approve","")

    But, I would like to use such a formula to look for "Sub" in a range of cells, for example, A2:F2 (1st case scenario), and then in a different instance, to look for "Sub" in a group of cells - A2, C2, D2 and G2 (2nd case scenario).

    Thank you very much in advance.

    Stan

      1. Kind greetings and thank you very much for your kind help / response. I tried the equation that you helped provide above but it still provides a blank response, even when one of the cells contains "Sub".
        In this case, I'm trying to approve the the row #3 because one of the cells from C3 to G3, in this case E3, contains the string "Sub".
        A B C D E F G H
        1 Alt ID Plans CovA CovB CovC CovD CovE Approval
        2 101020 Pol3 Plan11 Coord2e
        3 907030 Pol Sub5a Alt24
        4 805050
        5 778050 Plan88 Sub7d Coord2
        6 232520 Sub4 ALt4 Plan6
        7 357031 Plan2d Sub7e

        So, I used =IF(SUM(--ISNUMBER(SEARCH("Sub",C3:G3))),"Approve","") but it still gives a blank response.

        Thank you again, in advance.

        Stan

        1. Hi, thank you very much for your kind help. I read one of your blogs on Excel ISNUMBER function with formula examples, under conditioning with SUMPRODUCT and it helped.

          The equation I'm using now is =IF(SUMPRODUCT(--ISNUMBER(SEARCH("Sub",C3:G3))),"Approve","") and it is helping with what I needed to do.

          Again, your very kind help is much appreciated.

          1. Hello!
            You didn't specify this, but I'm assuming you're working with Excel 2019 or 2016. The formula returns an array that needs to be summed. In Excel 365, you do not need to do this.

            1. Sorry about that; and yes, I'm working with Excel 2019.

              Again, I thank you.

              Best regards,

              Stan

  31. Hi Alexander,

    I am new to extended formulas. I usually manage with Vlook Up and Pivots. I am trying to show value of cell B2 in cell C2, if Text in Cell A2 is specific and if not, then value in Cell C2 must reflect "0" / zero. Likewise, for Row no.s 3, 4, 5, ....

    Eg., A2 value is "Opening Balance", B2 value is "100" then C2 must reflect the value of Cell B2 i.e., "100". But if A2 value is NOT "Opening Balance" then C2 must reflect "0" / zero in numerical value. Please help me.

    A B C D E F
    Transactions Amount Opening Balance Invoice Debit Note Receipt
    1 Opening Balance 100.00 100.00 0 0 0
    2 Invoice 248.00 0 248.00 0 0
    3 Debit Note 10.00 0 0 10.00 0
    4 Receipt 238.00 0 0 0 238.00

    Thanks,
    Anand

    1. Hello!
      For your task, you can use the IF function.

      IF(A2="Opening Balance", B2, 0)

      If the suggested formula doesn't work for your case, feel free to describe it in detail.

  32. Great information. Is it possible to put a formula in a cell that tests a different cell and places text in a 3rd cell ?
    Example: Formula is in cell E1, testing cell A1 is equal to "Test", setting cell B1 equal to "Yes"
    Cell B1 has other text in it until cell A1 has been changed to "Test" manually.
    If, Then , Else structure but being able to specify the cells for the then and Else output.
    I used this structure but can not specify a target cell
    Cell B1 is =IF(ISNUMBER(SEARCH("Test,A1)),"Yes","No") will set B1 to Yes or No
    but wish to add target cells for results:
    Formula is in cell E1 =IF(ISNUMBER(SEARCH("Test,A1)),("Yes",B1),("No",C1)) Target cells are B1 and C1
    I know how to do it in Macro but then need to run the macro. I wanted it to happen without running or adding macro.
    Thanks,
    Vin

    1. Hi!
      It has already been written many times in this blog that an Excel formula can only change the value of the cell in which it is written. For all other tasks, use VBA.

  33. Hello, I would like to seek help. If a cell has a text value: then perform another formula else blank

  34. IF the cell contains numbers and characters and without numbers , how can i filter only character cells please help me

  35. Please help to write a formula for the below

    If I update the formula in I2 cell to =IF(SEARCH("HOSB",H2),"PO",""), the result is coming correctly, but if I change it to =IF(SEARCH("HOSB",H2),"PO",IF(SEARCH("HONB",H2),"Non PO",IF(SEARCH("HOCB",H2),"Contract", IF(SEARCH("HORB",H2),"Retention","")))) I am getting an error stating #VALUE!

    1. Hello!
      If the text is not found in the cell, the SEARCH function will return an error. Add an ISNUMBER function to your formula. In case of a successful search, it will return TRUE, in case the text is not found, it will return FALSE.

      =IF(ISNUMBER(SEARCH("HOSB",H2)),"PO", IF(ISNUMBER(SEARCH("HONB",H2)),"Non PO", IF(ISNUMBER(SEARCH("HOCB",H2)),"Contract", IF(ISNUMBER(SEARCH("HORB",H2)),"Retention",""))))

      1. hi,

        I intend to use this formula and I got a comment "This formulate use more level of nesting than you can use in the current file formate." How to fix this formula?

        =IF(ISNUMBER(SEARCH($M$4,I7)),$N$4,IF(ISNUMBER(SEARCH($M$5,I7)),$N$5,IF(ISNUMBER(SEARCH($M$6,I7)),$N$6,IF(ISNUMBER(SEARCH($M$7,I7)),$N$7,IF(ISNUMBER(SEARCH($M$8,I7)),$N$8,IF(ISNUMBER(SEARCH($M$9,I7)),$N$9,IF(ISNUMBER(SEARCH("ELECTRICITY",I7)),$N$10))))))

  36. Hi,

    Kindly help please
    A have a row for the report headers and below it a a row that says it is Mandatory or optional.
    How so i check if the mandatory columns have value?

    Thank you.

      1. Hi! Thanks for the response.
        But how do i make it dependent on the mandatory/optional row?

        For example if the following column headers are the following: name, address,mobile,birthday.( last column is the checking, row complete?)

        And then on the row below , all the fields are mandatory except for the birthday.
        * row complete value should be TRUE if the mandatory cells are populated.

        Thank you!

          1. Hi,

            Thanks.

            It would somehow look like this.
            But i dont get how will the code be dependent on the second row. (By checking “Mandatory “)

            Name Address Birthday Mobile Row complete?
            Mandatory Mandatory Optional Mandatory
            James ABC street 8171777 True
            Alice Aaa streeat 10/10/1970 81666 True

              1. Hi, the problem is that i need to check for row 2 as well( mandatory optional)
                Starting from row 3( i need to check if there are blank values from the cells tagged as mandatory)

                I actually have around 20 headers ( lets say from a1 to t1) and from a2 to t2 it says mandatory or optional.

  37. Hi Alexander,

    I been going crazy trying to get this formula, if you could help me that me very appreciated.
    What I am trying to do is

    (b1+b2)/2 if b1 or b2 aren't entered don't divide just give me the number that was entered in b1 or b2

    Thank you in advance

      1. Hi Alexander,

        Thank you so much for your help it help

  38. Hi,

    I need to find a formula where the number is contained within text in a different cell. For example:

    Column A Column D

    21 Address 21 London Road London

    There are 2253 numbers which I need to find within 4955 cells, please help!

    Many Thanks

      1. Hi,
        The only issue is this is only taking it from the first column, I would like it to look in the whole of column D to find the matching one?

        Many Thanks

        1. Hi!
          Copy the formula for each cell you want to extract numbers from. It is impossible to do this with a single Excel formula. If this is not what you wanted, please describe the problem in more detail.

  39. I am trying to write a formula that allows me to do the following:

    Column I has either USD or CDN dollars,

    If I has USD then take Colum G Total price and times it by currency rate listed in T2 or the rate 1.20

    1. Hi!
      If I understand your task correctly, the following formula should work for you:

      =IF(I1="USD",G1*T2,G1)

      1. Thank you so much, that worked. So kind of you.

      2. That was perfect - thank you so much I tried over 8 different variances of IF / OR and AND trying to get this to work. Your the kindest, thank you.

  40. Hello!
    If I understand your task correctly, the following formula should work for you:

    =IFERROR(INDEX(E2:E12,MATCH(1,--ISNUMBER(SEARCH(D2:D12,A2)),0)),"")

    Column E - ID
    Column D - search text
    Column A - Description
    Hope this is what you need.

    1. Yes!!! That works. I'm glad to see you used MATCH. I had played with SWITCH a little bit but I failed at that. Makes feel like I sort of had the right idea!

  41. I am using excel to convert manual testing scenario sheets to automated xml files to test the Covid vaccine schedule and ensure our vaccine forecaster is functioning properly with the new rules. I need to find out if a formula within a cell is calling the DOB or the date of the last vaccine for the forecast and then use that to fill in the test description so I can more easily spot patterns in what causes unexpected forecasting returns. Basically I need a formula that says IF the formula in GN2 (earliest forecast date) contains a reference to E2 (DOB) then True else false. Is there anything that can do that for me?

    1. Hello!
      I recommend using the FORMULATEXT function. It will extract the formula from the desired cell and write it down as text. Then apply the SEARCH function

      =ISNUMBER(SEARCH("E2",FORMULATEXT(F5)))

      I hope my advice will help you solve your task.

  42. Hello,
    I am trying to figure out a formula that will tell me if one cell partially contains the same info as another cell.
    Example:
    If A2 has "PleaseHelpMe" and B2 has "Please"
    I want a formula that will do the following IF A2 contains B2 = "yes" or "no"

    Hopefully that makes sense.

      1. Alexander,
        You are awesome! Thank you, this will help out tremendously on a project I am working on.

  43. Hello! I have a large spreadsheet (300k rows) with clients details, unfortunately the data is from a form where people were simply asked to enter their City & Country. So they may have entered Christchurch NZ, or Auckland New Zealand, or Los Angeles USA etc etc

    We now wish to be able to add a new column that specifies the country ONLY for each client.

    What is the best approach for this?

    Ideally we would like to be able to have one formula that can search for multiple countries, so for example, if cell A2 contains "NZ" OR "New Zealand" the value in the new column shows as = New Zealand, if the A2 contains "United States" or "US" or "USA" or "America" the value in the new column shows as = USA. everything I have tried so far says it is too long, so I assume I need to work out how to use Vlookup? Is this what it will do!?

    Obviously there is a huge array of possibilities, is it possible to have SO many variables? Thank you!

    1. Hello!
      You will be looking for a piece of text in a cell. Therefore VLOOKUP cannot be used here.
      Try this formula:

      =INDEX(F1:F30,MATCH(TRUE,ISNUMBER(SEARCH(E1:E30,A1,1)),0))

      Column F - correct country names (e.g. New Zeland)
      Column E - arbitrary country names (e.g. NZ)
      Column A is your data (e.g. Christchurch NZ).
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you. your formula assisted me in resolving my pain area.

  44. I have a chart with 2 columns, 1 column - Month 2nd column - Amounts
    how do I create a formula that pulls out or subtract out the amounts for a certain month.
    EXAMPLE: February amount needs to be subtracted from Jan, Mar, April.

    January 150
    February 200
    March 500
    April 2000

    I know to sum up everything and then subtract, but I don't know how to create the formula to do this on its own.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(B1:B4)-B2

      If this is not what you wanted, please describe the problem in more detail.

  45. Hello,
    I would like to match 3 cells in two separate tabs (same file), cells A (tab 1), and cells B & C (tab 2).
    Cells A & C are a string of words. Cell B is one word.
    If one of the words in cell C is found in cell A, then the formula returns cell B.
    I'm going through a bank statement and doing some budgeting based on categories I've created.
    For example:
    1/ Tab 2: Category "Utilities" (cell B) = Water, Council, BT (cell C)
    2/ Tab 1 (bank statement) (cell A): DIRECT DEBIT PAYMENT TO WATER REF 400000214, MANDATE NO 0125

    I haven't been able to work out the formula to use to bring back the value of cell B in a new column added to the statement (tab 1).
    Might you be able to help please?

    Many thanks,
    Cecile

      1. Hi Alexander,

        Thanks for your feedback. I was hoping for a shortcut, never mind!
        Once I've split the text, shall I then use the following formula:
        =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1","No")

        Thanks,
        Cecile

        1. Hello!
          Yes, you can use something like this.
          IFS function can be used to choose from several categories

          =IFS(SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1", SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$b$2:$b10,Tab1Statement!C2))),"categoryName2")

  46. Hello

    I have an incident where Students are graded in a subject per week from week one to week 16. where each week is in its own column from Column B to Column P. where I grade each student with words "Passed" and "Excelled". I'm crediting their overall performance in another sheet which fetches from my primary sheet and I would like help with an excel function that searches for a word "excelled" in any week from week one to 16 and returns "promoted" if it finds the word "excelled" any where. Thanks!

    1. Hello!
      To search for values in the desired line, I recommend using such a formula:

      =SUMPRODUCT(--(A2:A20=N1)*(B2:P20= "Excelled"))

      N1 - name.
      If the formula returns the number more than 0, then the desired word is found for this student.
      I hope it’ll be helpful.

  47. I.e. I have cell K2 containing 2 different categories (OWN & LEASE). If cell K2 is "OWN" I want to add values from cell P2+R2+S2 or if cell K2 is "LEASE" then I only want to add values from Q2

  48. Hi there, hoping I didn't miss this explained above or in the comments but here's what i'm trying to figure out:
    I have a list of titles in multiple rows of column I (Ex. I2 contains Associate, Manager, Senior Manager, Vice President. I3 contains Associate, Senior Manager, Vice President).
    I am using the following formula to separate each title into separate columns for each Row, if it is listed in "I" : =IF(ISNUMBER(SEARCH("Associate",I2)),"Associate") but I'm finding that when using the formula for Manager it is giving me a false positive because "Senior Manager" contains the word "Manager" (is should result in "FALSE").

    Basically, is there a way to add an exclusion for the word "senior" in the formula?

    Thanks!

    1. Hello Meghan!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(SEARCH("Manager",I3)), IF(ISNUMBER(SEARCH("Senior Manager",I3)),FALSE,"Associate"))

      I hope this will help

  49. Hello
    Is there a way of using the below formula, but rather than have it search for the specific text only within a cell, it can search a sentence containing "apple" or "banana" etc then return the value based on the sentence content? I need the formula to be able to search for multiple fruits and return the value in another cell depending on what fruit it found within the sentence.

    For example, cell A1 contains the sentence, "Mr Smith ate an apple".
    cell B1 should then return Apple. However, if cell A1 contained, "Mr Smith ate a banana", cell B2 should return "Banana".

    =IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))

    Hope this makes sense!

    Thank you

    1. Hi Rhys,

      COUNTIF with wildcards in the criteria works a treat:

      =IF(COUNTIF(A2, "*apple*")>0, "Ap", IF(COUNTIF(A2, "*avocado*")>0, "Av", IF(COUNTIF(A2, "*banana*")>0, "B", IF(COUNTIF(A2, "*lemon*")>0, "L", ""))))

      1. Thanks so much! Worked perfectly.

  50. what would I use in the formula to lookup if a cell has text or number? (replacing the ISNUMBER) ISTEXT will not work as the cell can contain text or a number.
    =IFERROR(IF(B17="","",IF(ISNUMBER(INDEX(T_E,MATCH(I_E,L_E,0),MATCH("ACT "&B17&" DT",L_H,0))),"R",CHAR(163))),"")

    1. In your MATCH formula, what is the T_E , I_E and L_E? I believe that should be a range, but what range is it referring to?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)