How do I use a nested IF(AND) in an Excel array formula?

65,024

Solution 1

Try to use next array formula:

=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")

here is a link to the test workbook.

Solution 2

You can nest an IF inside the value of true of an IF to achieve an IF something AND somethingelse. For example :

=IF(A1="something", IF(A2="somethingelse", "A1 is something and A2 is somethingelse", "A1 is something but A2 is not something else"), "A1 is nothing and A2 is nothingelse")

Some extra details on trying this code out : write "something" (without quotes) in A1, and "somethingelse" in A2, put that formula inside any other cell of that sheet. Play around with those values to see where you land in the nested IFs.

Share:
65,024
Steven T. Snyder
Author by

Steven T. Snyder

I have quite a collection of hats, and wear most of them professionally on a daily basis. Software engineer, electrical engineer, systems administrator, graphic designer, scientist, product designer, and more. I work for a think tank / R&D company in Southern California.

Updated on July 19, 2022

Comments

  • Steven T. Snyder
    Steven T. Snyder over 1 year

    How do I get a nested 'AND' to work inside 'IF' in an array formula?

    I reduced my problem to the following example: Excel spreadsheet showing array formula with nested AND Note: the above image has been updated to included the array formula curly braces

    At the top right, we have the search criteria in L3 ("color") and L4 ("shape"). At the left, column D contains working match formulas for both color and shape in the list of items. The first table shows the match formula working properly without using an array formula.

    The second table shows an array formula that matches the color.

    The third table shows an array formula that matches the shape.

    On the right is my attempt to use both criteria in an array formula, by combining them with AND.

    IF the value in the color column matches the color criteria (L3) and the value in the shape column matches the shape criteria (L4), then I want to see "MATCH!".

    I did find a workaround: concatenate the values and criteria, and then match them inside a single IF. I feel like there should be a Better Way... like if AND worked as expected!

    Note: Many of the answers below work correctly but not as array formulas, which is specifically what this question is about. I looked at my original question and realized I forgot to show the curly braces in the array formula examples. I have fixed the image to show them. Sorry for the confusion.

    The key to answering these questions is to write something that works as an array formula, which is entered by pressing CTRL+SHIFT+ENTER after typing the formula into a cell. Excel will automaically add the curly braces to indicate that it's an array formula.

  • Steven T. Snyder
    Steven T. Snyder about 10 years
    This isn't using IF as an array formula, and this solution breaks if an array formula is used. I just realized my original question doesn't show the array formula curly braces, so maybe that's confusing the question. I will fix it.
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    This works great! The specific solution in my question is {=IF($J16:$J22=$L$3,IF($K16:$K22=$L$4,"MATCH","-"),"-")}
  • Dmitry Pavliv
    Dmitry Pavliv about 10 years
    It's exactly array formula and it works well for me. If you select L16:L22 range and with selected range enter the formula in the formula bar, and them press CTRL+SHEET+ENTER - you'd get the result you need (and curly brackets will apear). I've tested formula many times
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    Using the * operator does not make it an array formula (office.microsoft.com/en-us/excel-help/…)
  • Dmitry Pavliv
    Dmitry Pavliv about 10 years
    and this solution breaks if an array formula is used - How it breaks??? Have you really tried it to use like array function? And what are you going to do if you'd have 10 criteries (not 2) - are you going to use 10 nested IF's??
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    It just gives a MATCH result for everything. Did you try it as an array formula? Here's a screenshot: imgur.com/QMU41eM
  • Dmitry Pavliv
    Dmitry Pavliv about 10 years
    Hm..here is my example workbook..try it, please. dropbox.com/s/7m1amdjfxo4lzq8/Simoco_Test.xlsx
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    Thanks. Your worksheet works perfectly fine, and the formula is entered as an array formula. We have the same formulas entered, but in my test worksheet it doesn't work. Weird!
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    Got it to work; I didn't have all the cells selected when I pressed SHIFT+CTRL+ENTER. Could you edit your answer to include the link to your workbook? SO isn't letting me change my vote without the answer being edited.
  • Steven T. Snyder
    Steven T. Snyder about 10 years
    Thanks! I've changed my accepted answer to this one, since it's scalable to more criteria.
  • Bernard Saucier
    Bernard Saucier about 10 years
    Indeed my solution was very hard to scale! +1 to this excellent alternative.
  • Paris Char
    Paris Char about 9 years
    This is so elegant and excellent solution. and for OR operations, use + instead of * . POI: Array formulas on a mac: shift-command-enter