Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 35. Total comments: 4461

  1. Hi there,
    I am trying to create some conditions within a cell and I am really struggling with a formula. I have 30,000 rows with website information. I am trying to categorise each site into Entertainment, News, Sport and use key words to populate a cell, .
    So I want to create a rule that says something like this:
    If Cell B3 contains Sport cell D3 = sport OR if cell B3 contains News D3 = News OR If cell B3 contains Entertainment Cell D3 = Entertainment. How do I turn this into an excel formula?
    the problem is that cell B3 contains other words, not just sport or news...so what I want to do is apply it to the string and not an exact match. Does this make sense, is this even possible?

  2. Hi I am trying to combine several IF formulas into one - the formula used to calculate what I want depends on the value of the variable. For example:

    If BW12 is <=1 then the formula is 1-BW12^2/2
    If 1<BW121.7 then the answer is 0.05

    I know what I have currently:

    =IF(BW12<=1,(1-(POWER(BW12,2)/2)),IF(1<BW121.7,0.05)))

    is wrong so I would appreciate any assistance,

    Thanks

    1. Lily:
      What is the meaning of this piece, "1<BW121.7"?

  3. Hi. When there are no amounts entered on a sheet, what formula do I enter so this, #DIV/0! is recognised as a 0 amount as my calculations will not work with this symbol present?
    Thanks

    1. Jim:
      It depends on what version of Excel you're using but,
      after Excel 2007 you can use =IFERROR(A2/A3,0). This tells Excel if your formula evaluates to #DIV/0!, then return 0, otherwise return the result of the formula.

  4. Help with coming up with a formula!!

    COUNTIFS('EMPLOYEE Detail'!E:E,"TWIN LAKES MEDICAL FOUNDATION INC",'EMPLOYEE Detail'!H:H,"EMPLOYEE")+COUNTIFS('EMPLOYEE Detail'!H:H,"EMPLOYEE",'EMPLOYEE Detail'!G:G)

    It keeps giving me an error. I am trying to get the twin lakes medical foundation and the employee information which are from two different columns to give me a $total from another column.

    1. I am working off of 3 columns to try to get the data I need, I was able to get it to count how many employees have selected the product I am just stuck on trying to get it to calculate as to which company the employee is with to get me the $ they have elected.

      Column E Column G Column H
      Stoutco 14.18 Employee
      Cedar 22.54 Employee + Spouse
      Stoutco 22.54 Employee + Spouse
      Cedar 40.14. Employee + Family
      Cedar 22.54 Employee + Spouse

      I came up with this formula to get me the total for each COUNTIFS(E:E,"STOUTCO",H:H,"EMPLOYEE) to get me a total of 1.

      I just need help with coming up with Stoutco, Employee = Column G for the whole column to pull how many people elected the products.

      Please and Thank you,
      Stephie

  5. Uses the if
    =If(and(A1time(12,30,00)), 60 , If(and(A1time(18,00,00)),30,0). Just cudnt fiq out bracket. How do I add another condition so that is display the number 30 also? But the time is A112,45,00?

  6. Hi Svetlana,
    I need a formula that would do this,
    If colum A has the same name of column B and the value at Column C is bigger than zero, then multiply the number on column C by the number on Column D. Can I get that?
    Thanks
    Bob

  7. Very helpful stuff! Thanks much!

  8. I am trying to build a formula for the following:

    If Cell C7 has a "Y" in it and Cell E7 is blank then Cell G7 needs to say "To Print" otherwise leave no wording in G7.

    I know the experts will scoff at the simplicity of this but I can't fathom it, all help appreciated!

  9. I'm not a regular Excel user so I apologize in advance.
    I need to create a column with a IF formula that indicates whether a "yes" is populating any of the 4 previous cells in each row.

  10. need help too.
    if after computing the grades in a column1 and on column2 the cell will automatically comment on the rate of poor, average, and excellent (depending on the grade bracket). how to create formula to put the grade on 2nd sheet with columns of poor, average, and excellent. but on the cell, the grade on the 1st sheet will automatically be transferred on the correct column range.

    i.e.
    No. / poor / average / excellent
    P1 75
    P2 85
    P3 84
    P4 90

    thank you

  11. How to apply IF condition if the days fall in this category

    >45 Days
    31-45 Days
    16-30 Days
    0-15 days

    Is this correct :=IF(H9<=15,"0-15 Days",IF(H93045 Days")))

    1. Manish:
      Can you make your question clearer?
      What does "IF(H93045 Days") mean?

  12. Can I use this for sorting out carry out in result computation? If not help me out please

    =IF(AW9<40,"TSL 102",IF(AR9<40,"FOT 213",IF(AM9<40,"FOT 212",IF(AH9<40,"FOT211",IF(AC9<40,"CME 122",IF(X9<40,"WPT 216",IF(S9<40,"WPT 214",IF(N9<40,"AGT 231",IF(I9<40,"GNS 201",IF(D9<40,"FOT 214",))))))))))

    1. Fadele:
      I don't believe the nested IF statement you included in your post will provide anything useful.
      Try breaking it down to smaller pieces that help answer a question.
      Put the IF statements in separate cells to check the logical value in each cell.
      Check here on AbleBits and read the articles about IF and nested IF statements and I believe you'll see how the IF statement should be written.

  13. If March, April , May Sale is 0 & June Sale is 1 then True Or False

  14. if((and(F8="HOME LOAN",J8>0.80%)),"APPLICABLE","NOT APPLICABLE"),if((and(F8="LAP",J8>1.10%)),"APPLICABLE","NOT APPLICABLE"),if((and(F8="LRD",J8>0.50%)),"APPLICABLE","NOT APPLICABLE")

    Result: #VALUE#

    Can any one Help me to fix the problem in the Above formula

  15. Hi. I have 3 columns. a credit column, a debit column, and a total. if there is a value in the debit column, i want it to deduct from the total. if there is a value in the credit column, i want it to add to the total. there con only be a debit or credit for each record.

    1. James:
      If you have the Excel version that allows you to create tables, consider creating a Table for this data. The Table would have six columns. They would be from left to right:
      Item, Credit, Running Credit, Debit, Running Debit, Running Total.
      Where the first data cell is B6 The Running Credit would have =SUM($B$6:$B6) this would create a running total of the credit.
      The Running Debit would have $D$6:$D6 in its top cell. This would create a total for the running debit.
      The Running Total would have in its top cell =SUM($B$6:$B6-$D$6:$D6) which would create the running total.
      At the bottom of each column in a Table you can quickly create a sum or various other stats.
      You can insert a row in the Table to include a new item and the totals will be updated as soon as you enter the value.
      You can sort the data quickly and easily while maintaining the relative relationships.
      You can also format the Table with nice looking formats.
      AbleBits has an article that explains Tables and their advantages.

  16. Not sure if this is the right place but I have the following two problems and don't know if there is a solution so please help

    Problem 1

    In cell A1, I enter the gender, B1 the age and C1 the score and according to this information, D1 by means of a formula must indicate Pass or Fail. But the pass mark differs according to gender and age. For example Female younger than 35 must achieve 80% to pass, Male younger than 35 must achieve 90% to pass. Female older than 35 must achieve 60% to pass and Male older than 35 must achieve 70% pass. What can I do that when I change the gender or age then the applicable formula (pass mark) is automatically selected in cell D1

    Problem 2

    I have three cells with scores (A1=60, B1=70, C1=50) I want the highest score of the three to be indicated as a value in cell D1 and E1, by means of a formula, indicates the text Pass or Fail or Did not Write (60 being the pass mark). Yes I did use the max function for D1 and IF function for E1 but here is the challenge. If I enter a text like "DID NOT WRITE" in anyone of cell A1,B1 or C1 then the highest score must still be indicated in cell D1. And if the text "DID NOT WRITE" is entered in all three cells then I want cell D1 to indicate a text like FALSE or something so that the formula in E1 can indicate "DID NOT WRITE"

    I will really appreciate some help in this regard if possible.

    Thank you in advance

  17. Hello.

    I am hoping for some help building a formula.

    I want to create a sequence of numbers that increases as it goes down the column. For Example A1 = 1. A2 = 2. However, I am hoping to create a condition that if the G2 contains a value that is fully encompassed within G1 then the A column repeats the value from above. For Example if G1 = zyzy121 and G2= zyzy, then I would A2 to use the same number as A1. I would then like A1=1, A2=1, and A3=2

    Let me know if you have any ideas. Thanks

  18. Hello, i am seeking a better way
    currently i have a data list (down)with
    name,acquisitionday,units, across the top of my excel file i have a data list (across) with name,rateday,rate.
    my current formula =if(name across matches name down,(if((acquisition date<rateday,round(units*rate,2),0)),)

    1. continuation,
      seeking to have 1 tab with down data, and 1 tab with across data
      on the down data tab, seeking to insert a nested if function to calculate results
      down tab
      abc,9/19/17,100 result to be 25, as the last rateday is after the acquisitionday

      across tab (rates and dates can be different)
      abc, 6/1/17, 0.10
      abc, 8/1/17, 0.15
      abc, 10/1/17, 0.18

  19. My Quistion is that

    like column-1 is 20-30 and column-2 is 5 then remark is 400 + column-1, 31-40 and Column 2 is 7 then remark is 700 +column -1,41-50 and column 2 is 10 then remark is 10000 + column -1, 51-60 and column -2 is 12 then remark is 12000

  20. Hi,
    I am looking to create a formula that looks in different cells (for example: A1, B1, C1, D1) and if these cells have "no" then it skips until it finds something different than "no" and put in the cell where the formula was written whatever text was in that cell.

    thanks

    1. Oren:
      I believe this will produce the result you're after.
      Where the list is structured as a vertical list in column A cells A1:A100, enter this in B1:
      =IF(A1="No","",A1) and copy it down the B column.
      This formula says, If A1 has "No" in it then leave B1 empty, otherwise enter what is in A1.
      You can type in whatever text you need by typing the text between the quotes holding No or the empty quotes.

  21. Hi,
    I am looking to create a formula that looks in different cells (for example: A1, B1, C1, D1) and if these cells have "no" then it skips until it finds something different than "no" and put in the cell where the formula was written whatever text was in that cell.
    For example cell A1, B1, & D1 have "no"and cell C1 has "apply" the result would be apply

    thanks

  22. hi,
    i am looking for a formula for wages calculation with below condition,
    --if "X" person works same day in Same place in different session or one session(AM, PM, and Evening session), he will be paid only GBP 3.00 amount(his working hour in each session are mentioned in different rows).

    --if he works in different place in same date he will be paid GBP 3.00 for each place.

  23. Thanks Doug

    Got this one to work

    =IF(IF(D7=0,0,IF(D7<=59,1,2))=2,IF(H7=1,2+5,2),IF(IF(D7=0,0,IF(D7<=59,1,2))=1,IF(H7=1,1+2,1),0))

  24. It there anyway I can send the formula other than in this thread ?

    Again the formula above is incomplete, its only showing 5 IF's but I have 6, it keeps dropping IF(D759,"2"

  25. This is both together but it's only returning the clean sheet and not the points for minutes played

    =IF(D7=0,H7*D7,IF(D7>=60,H7*5,IF(D7<=59,H7*2,IF(D7=0,"0",IF(D759,"2"))))))

  26. Hi Doug

    This works for the clean sheet
    =IF(D7=0,H7*D7,IF(D7>=60,H7*5,IF(D7<=59,H7*2)))

    This works for the mins played
    =IF(D7=0,"0",IF(D759,"2")))

    Just can't get them to work together.

  27. Hi Doug

    I've tried it again but it's still not returning what I need.

    What I'm trying to do is if someone plays less than 60 mins they get 1 point for playing and 2 for a clean sheet if they play 60 or more they get 2 for playing and 4 for a clean sheet and finally if their playing time shows 0 then 0 for playing and 0 for clean sheet.

    =IF(D8=0,"0",IF(D859,"2")))

    This works for the minutes played but I'm trying to get the clean sheet in the same formula.

    Thanks for all your help.
    This is making me greyer by the minute..

    Stewart

    PS my clean sheet is in column 8, so I enter a 1 if they play

  28. Again its dropped the middle, not sure what is up but if(d7<=59,"1" is missing again

    1. Stewart:
      Not sure what is happening on your Excel sheet, but the formula I sent works fine on mine. As to the other issues you're seeing, I don't know how to help with that.

  29. Nope its reverted back to the same one cutting out the middle which is ,IF(D7=60,"2"))) that follows =if(d7=0."0",

  30. Again, I'm not sure what is happening but the copied formula is fine my end but soon as it's posted it not the one I copied..

    "=IF(D7=0,"0",IF(D7=60,"2")))" tried it with "" to see if it sticks"

  31. Sorry still attached the wrong one a second time
    =IF(D7=0,"0",IF(D7=60,"2")))

  32. Hi

    I can't get this to return.

    =IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2))))))

    Thanks

    1. Hello Stewart:
      Try this:
      =IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2)))))

      1. Thanks Doug

        But now my 60>= doesn't return my 2.

        This is what I am trying to do.

        if sheet >= 60mins then 2 points, if less than 60 mins 1 point, if 0 mins then 0 points + if clean sheet =1 then if cell with minutes >= 60 mins then 5 points, if less than 60 mins 2 points and if 0 mins 0 points.

        Hope this helps, I'm really struggling to get the 3 if's + another 3 if's to work together.

        On there own they work
        This is the clean sheet one
        =IF(D7=0,0,IF(D7>=60,H7*5,IF(D7<=60,H7*2)))
        This is the mins played one
        =IF(D7=0,"0",IF(D7=60,"2")))

        I need them to work together if possible

        1. Sorry minute one is this
          =IF(D7=0,"0",IF(D7=60,"2")))

  33. hello
    thanks for your help
    how to do this formula ???
    if one column is bad d5 =0
    else if another column is good d5 =10

    1. Hi Ali,

      if(d5=0,” Bad”,”Good”)

  34. I keep getting a #N/A when the cells that use this formula are blank. I want the formula cell to show blank when there is no information to complete the calculation. Here is the formula that is in the cell.

    =IFS(W6=1,T6/AD8,W6=2,T6/AD9,W6=3,T6/AD10,W6=4,T6/AD11,W6=5,T6/AD12,W6=6,T6/AD13,W6=7,T6/AD14,W6=8,T6/AD15,W6=9,T6/AD16,W6=10,T6/AD17)

    I have tried multiple ways of using "" but I can't figure out how to not get either the #N/A or the FALSE to show in the formula cell.

    1. Callie:
      I found this solution on the web. The solution was for a question like your's concerning the blank cell issue.
      You should replace the HLOOKUP parts with your IFS pieces. As the above article explains you can also trap that #N/A error with IFNA or ISNA.
      As an aside, the number of IF variables in your formula makes your situation a very good candidate for another approach. There are way too many IF then parts to keep up with.
      Anyway, I believe you're using Excel 2007 or newer, but I included the first part for the folks on here who are using older versions.

      Excel 2003 or older:
      =IF(ISERROR(HLOOKUP($D$6,Benefits,2,FALSE)),"",HLOOKUP($D$6,Benefits,2,FALSE))

      Excel 2007 or newer:
      =IFERROR(HLOOKUP($D$6,Benefits,2,FALSE),"")

  35. IF CELL ADDRESS B5

    I WANT TO APPLY IF FORMULA FOR TWO/THREE CONDITIONS FOR THE SAME CELL ADDRESS

    EXAMPLE
    B5>500000,"B5-500000",IF(B5>=1000000,"1000000", "0")

    KINDLY GUIDE ME WHAT KIND OF FORMULA SHOULD BE IMPLEMENT IN SUCH CONDITIONS AND HOW?

    1. You have applied a wrong formula, please find the correct formula given below-

      =IF(B5>500000,B5-500000,IF(B5>=1000000,1000000,0)

    2. Shivaji:
      I think what you're looking for is:
      =IF(B5>500000,B5-500000,IF(B5>=1000000,1000000, 0))
      If the numbers are actually text then you need to enclose them in quotes, otherwise you leave them as shown here.

  36. I need to calculate a policy amount that depending on how it’s being paid, what amount of the policy we would get for that year. I’ve started what I think it the right formula but I’m getting an error and I’m not sure where I’m wrong. I’ve broken out the formula for each option and hope that it will work when all put together. I know that they all work individually except for Quarterly. I can’t seem to figure that one out. Any help you can give me would be great!
    One Time = Total Annual Policy
    Annually = Total Annual Policy
    Semi-Annually = If Jan-Jun then Total Annual Policy; If Jul-Dec then ½ of Annual Policy
    Quarterly = If Jan-Mar then Total Annual Policy; If Apr-Jun then ¾ of Annual Policy; If Jul-Sept then ½ Annual Policy; If Oct-Dec then ¼ Annual Policy
    Monthly = Total Annual Policy divided by 12 times months left in year (including month it was issued). (Example: Policy issued in May, Policy amount $400, (400/12)*(13-5) = 266.64)
    =IF(E7="One Time",J7),
    IF(E7="Annually",J7),
    IF(AND(E7="Semi Annually",Q7<=6),J7,J7/2),
    IF(E7="Quarterly",IF(Q7<=3,J7),IF(Q7<=6,(J7*0.75)),IF(Q7<=9,(J7*0.5)),IF(Q7<=12,(J7*0.25))),
    IF(E7="Monthly",((13-Q7)*(J7/12)))

  37. Hello

    I've read your guidance and created the following formula to achieve the following. Unfortunately it doesn't work. Any advice would be greatly appreciated.

    If L3 is empty return 'No Appt',

    If the date is L3 is in the future return 'Future Appt'

    and If both R3 is empty AND the date in L3 is in the past return 'No Report'

    =IF($L3="","No Appt", IF(L3>TODAY(),"Future Appt", IF(AND($R3="", L3<TODAY(),"No Report"))))

    1. Jonathan:
      Start building this IF And Statement from the inside and see where it breaks.
      Begin by putting IF($R3="", L3<TODAY(),"No Report", "T") in a cell. See if that will calculate.
      Then continue to build the formula piece by piece and you'll see where it breaks. When it does this may help you to determine what your goal is and how to accomplish it in another way.

  38. I'm looking to create a formula to dumb down some accounting entries. I have 4 variables, Taxes Payable, Taxes Receivable, Increasing, Decreasing

    If the balance sheet shows Taxes Payable AND the provision for taxes is increasing, then Debit 702 & Credit 202

    If the balance sheet shows Taxes Payable AND the provision for taxes is decreasing, then Debit 202 & Credit 702

    If the balance sheet shows Taxes Receivable AND the provision for taxes is increasing, then Debit 702 & Credit 124

    If the balance sheet shows Taxes Receivable AND the provision for taxes is decreasing, then Debit 124 & Credit 702

    I'm not sure exactly how to do this.
    Thanks!

  39. Adams:
    It's not clear from the wording of your question exactly what you want to accomplish, but in general questions like your's are asking about this situation:
    You enter a value in a cell, let's say cell A221 and when the value is entered you want to display a grade like "Good" in a different cell, say B221. If this is what you want the formula you should enter in the display cell (B221) is:
    =IF(A221<=34,"Fail",IF(A221<=44,"Pass",IF(A221<=54,"Pass",IF(A221<=75,"Credit",IF(A221<=84,"GOOD",IF(A22194,"Excellent")))))))
    If your version of Excel supports IFS statements, look up that term here on AbleBits and see how to build a cleaner looking formula.

  40. please i need help how do i find a formula to this
    i have to create a school report with a score mark and grade
    A1 100-95 EXCELLENT
    B2 94-85 VERY GOOD
    B3 84-75 GOOD
    C4 74-65 CREDIT
    C6 64-55 CREDIT
    D7 54-45 PASS
    E8 44-35 PASS
    F9 34-0 FAIL

    please i need the formula to this HELP

  41. I have Two columns one for Ps1 and k12 , I want to calculate the value of k12 to comply with these three conditions:

    1- if Ps1<=10 then k12=1
    2- if 10<=ps1=20 then k12=200/(ps1)^2

    Thanks in advance for any help

    1. Nazar:
      The first condition looks to be something Excel can determine.
      The second condition is unclear.
      If PS1 is equal to 20 it can't be equal to 10 at the same time.
      Do you mean "IF PS1 is between 10 and 20"?
      Can you clarify the conditions?

  42. I have to columns , one for ps1 and another for k12, and I want to calculate k12 to comply with this three conditions:
    1- for ps1<=10
    use k12=1
    2- for 10<=1.5-0.05ps1=20
    use k12=200/(ps1)^2

  43. So confused and need this to come together....
    Hi all, I'm hoping someone can help. I'm working on a large report for our department and need to filter what is a mispicked item and what is a genuine short-supplied item but I've tried multiple variances and still can't clear this up as per table below.... I need to match the invoice numbers but then check if Credit Code is a 4 and a 5 from that invoice to show "Mispick" but if the invoice numbers don't match and there are two "Short" credits then the filter needs to show "Short" as per table:
    My current code and results below but I am still getting false "short" results....... all help would be massively appreciated.

    =IF(OR(AND(G2=5,G3=4),AND(G3=5,G2=4),AND(OR(C2=C3,OR(C3=C2)))),"Mispick","Short")

    Inv # Code Slot Cases Credit Code Reason Results:
    738527 136890 513211 1 5 Over Mispick
    738527 45599 513222 1 4 Short Short (wrong)
    735549 141191 C10912 2 4 Short Mispick
    735549 593377 C11212 2 5 Over Short (wrong)
    761848 156729 402831 1 5 Over Mispick
    761848 134880 403021 1 4 Short Mispick
    761848 649776 740421 1 5 Over Mispick
    761848 637876 741011 1 4 Short Short
    762648 278210 CD0813 1 4 Short Short
    765184 104500 290511 1 4 Short Mispick
    765184 283622 290531 1 5 Over Mispick
    764902 104500 290511 1 4 Short Short
    762670 104500 290511 1 4 Short Short
    758860 49658 C30111 1 4 Short Short
    764905 662505 294132 1 4 Short Short

  44. Very useful information!. Thank you.

  45. dear all

    pls advise for the excel formula for below condition

    i have two rate for multiple currency ,if amount is greater then eur 10000 the rate 71 other wise 72, if amount is greater then USD 10000 then rate 65 other wise 66.
    formula required for below task

    Currency 10 lacs
    USD rate 67.50 67.2
    Euro rate 82.01 81.65
    Pound rate 93.49 93.07

    Currency Amount Rate
    USD 100000.00 ?????

    1. Parkash:
      If this is the condition where currency in in column A the rates are in columns B and C, then USD dollar amount is in column B and the final amount is in column C this is what this looks like:

      Currency
      USD rate 67.5 67.2
      Euro rate 82.01 81.65
      Pound rate 93.49 93.07

      USD $100,000.00 $6,750,000.00

      The formula to achieve this is:
      =IF(B6>=10000,(B6*B2),(C2*B6))

  46. Would you help me remedy this formula?

    =IF(T2=3)AND(I2>1)AND((M2-AC2)>(I2*.7)),"N","Y")

    the second qualifier is my issue. T2 greater equal to or greater than 3 AND I2 greater than 1 AND Value of M2-AC2 is greater than I2*7, return N, if not return Y.

    1. Tim:
      I believe this needs to be an IF AND statement like this:
      =IF((AND(T2>=3,I2>1,M2-AC2>I2*0.7)),"N","Y")
      Notice the T2 >=3 per your logic.

  47. Inder:
    I would solve this problem with VLOOKUP. AbleBits has a good description for beginners. You should read it so that you'll better understand what I'm recommending.
    This is how I'd solve your issue.
    In a workbook called "Tolls 2018" I would create two worksheets. One named "TollAmt" and a second sheet named "AprilToll". Incidentally, whenever I do this for a client I create one sheet for each month of the year in a book titled for that year. This keeps the data organized by date, which is how they eventually need it.
    "TollAmt" has three columns. First column is PlaceNum, second is PlaceName and the third column is TollAmount. Enter the appropriate data in each column. For example, PlaceNum is 1 thru 100, PlaceName is AAA thru ZZZ and TollAmount contains the toll amounts for each toll place.
    Now, in the "AprilToll" sheet create five columns. The first column is named Date, the second is named TRK #, the third is named Toll Place, the fourth is named Place Name and the fifth and last column is named Toll Amount.
    I'm assuming you're tracking some kind of vehicle's toll expenses, so I named the second column TRK # for truck number, but you can call it whatever you want. You'll enter data into the first three columns manually. The fourth and fifth columns are where the magic happens.
    Enter this into AprilToll cell D2. =VLOOKUP(C2,TollAmt!$A$2:$B$100,2,FALSE)
    Enter this into AprilToll cell E2. =VLOOKUP(C2,TollAmt!$A$2:$C$100,3,FALSE)
    Now watch the magic!! When you enter the Toll Place, Place Name and Toll Amount are populated automatically.
    If you need the PlaceNum, PlaceName, and TollAmount in one cell just concatenate them like this: =CONCATENATE(C1&E1&D1)
    However, if you keep the data separated you can more easily analyze it by date, truck, place name, etc.

    1. Hi,

      Really thanks for your help. I have tried your formula in different way and it works !! many thanks again ..

  48. Hi,
    I have a big list (almost of 100 places with different toll amounts) of toll names and amounts. Every month I have to prepare all the toll list manually.I want to make a sheet where I put the place name in a particular column and the other cell (in column) take value of its toll amount automatically. I have tried to put multiple if formulas in a single cell but it doesn't works or showing error.

    Can you please help me on this.

    1. Are each of the place names unique?

      1. yes !!

        for an example.

        place 1 ABC , amount - 100
        place b BBB , amount - 200
        place c, BBD , amount - 700
        Place d, e ,f,g,h........

        I need all the condition in a single cell. I just want to put the place name in a cell and the another parallel cell should take the amount automatically !!

  49. I want to use astrick (*)in If formula eg coloumn A1to A5 equalor greater than 35 pass, less than 35 fail and A1 to A5 any cell 35* promoted. For these three conditions please suggest formula.

  50. =IF(OR(AND(E5="Critical",F5="OK"),1), AND((E5="Critical",F5="NOK"),0),"")

    What is error here

    Thanks in Advance

    1. Hello, Reza,

      It looks like the problem is in the usage of the logical AND / OR functions. Please read how to correctly use these functions here.

      Or if you describe the conditions for your formula in detail, we'll be able to specify what is wrong with the formula.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)