+ Reply to Thread
Results 1 to 4 of 4

Get One Value from Array Formula

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    wwr
    MS-Off Ver
    Excel 2010
    Posts
    16

    Get One Value from Array Formula

    I have an array formula as follows :-

    ={IF(($C$11:$C$15000=C11)*($AT$11:$AT$15000-BM11<0.001),$B$11:$B$15000,"")}

    As can be seen above, I have two IFs which are comparing values in Column C and Column AT and returning values in Column B.

    Always there shall be only one unique row in column B which shall satisfy the above two conditions.

    Thus, I understand that the above formula will return an array of 15000 values in which there would be one value which is not null ("") and all other values shall be null ("").

    However, I am not able to get that one value in Column B.

    If I use MAX or SUM, I am able to get that value as follows :-

    ={SUM(IF(($C$11:$C$15000=C11)*($AT$11:$AT$15000-BM11<0.001),$B$11:$B$15000,""))}


    But the value in Column B shall not necessarily be a number. It can be string also.

    I have tried CONCATENATE but not getting any value.

    How to achieve this ?

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Get One Value from Array Formula

    Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: Forgot the MAX on first try
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    03-14-2012
    Location
    wwr
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Get One Value from Array Formula

    Worked Perfect !!!

    Thanks a ton for quick reply.

    However, I would want to know why concatenate did not work ?

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Get One Value from Array Formula

    CONCATENATE does not work with array formulas unfortunately. Biiig mistake by Microsoft if you ask me.
    The best workaround is probably a UDF. Here is a random link to some UDF's: http://www.mrexcel.com/forum/excel-q...y-formula.html


    Edit: I see now that you tried to use CONCATENATE with a range in the end rather than as a part of an array formula. Well, it doesn't work with ranges either, just individual cells. For the record you might just as well use the & operator, it's equally handicapped but at least it's 10 letters less to type.
    Last edited by Jacc; 03-28-2015 at 06:28 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  2. 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
  3. 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
  4. Converting 3x10 array to a 1X30 array to run a Match formula
    By NBVC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2009, 07:45 AM
  5. [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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1