+ Reply to Thread
Results 1 to 2 of 2

Wrapping an IF around an Array Formula?

  1. 08-16-2015, 11:33 PM #1
    Motox
    Motox is offline
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    Wrapping an IF around an Array Formula?

    Hi Forum,

    How would I wrap an IF around this Array formula?

    I want it to return the result of the Array formula in cases where there is a value in the cell in column E:E, but return "N/A" in cases where there is no value in the cell in column E:E.

    {=MAX(IF(Sheet2!A$2:A$7=E2, Sheet2!B$2:B$7))}

    Thanks very much

    Dave
    Reply With QuoteRegister To Reply
  2. 08-17-2015, 12:09 AM #2
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Wrapping an IF around an Array Formula?

    Maybe this?

    =IF(E2="","N/A",MAX(IF(Sheet2!A$2:A$7=E2, Sheet2!B$2:B$7)))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Cheers!
    Deep Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  2. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  3. Formula for wrapping the text
    By rms10 in forum Excel General
    Replies: 1
    Last Post: 06-26-2009, 05:34 PM
  4. Wrapping text on formula
    By maxthebear in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-25-2009, 05:37 PM
  5. Wrapping a formula in Sum()
    By teylyn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2008, 10:34 PM
  6. Text Wrapping Cells that are part of a formula
    By Dahlman in forum Excel General
    Replies: 1
    Last Post: 01-26-2006, 02:00 AM
  7. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1