+ Reply to Thread
Results 1 to 3 of 3

Array that brings a value to a set of mulptiple cells

  1. #1
    Registered User
    Join Date
    12-24-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Array that brings a value to a set of mulptiple cells

    Hi,

    I would like your help with a formula that performs the following calculation in column D. I don't not want to use VBA or a combination of Pivots and Lookups to perform the calculation, I will only need a formula solution. To explain the example of the formula I need,I am using the table below.
    Every Record ID has multiple milestones. One of these milestones may have a unique Date plan identified (i.e. OBS1 and OBS 3 have dates but OBS 2 has none). The formula I would like to build will assign the date identified to all milestones corresponding to the same record id. I have previously used an array formula to calculate the same concept but that time instead of dates I was using Boolean values or the same text which was easy to do with an IF(SUM(IF( arrays and statements). In this case the date identified field becomes a new variable and I am not sure how to enter that into my formula.

    A- B- C- D
    1- ID- Milestone- Date Identified- Formula result
    2- OBS1- 1- X- 6/17/2016
    3- OBS1- 2- 6/17/2016- 6/17/2016
    4- OBS1- 3- X- 6/17/2016
    5- OBS2- 1- X-X
    6- OBS2- 2- X-X
    7- OBS3- 1- X- 7/20/2016
    8- OBS3- 2- X- 7/20/2016
    9- OBS3- 3- 7/20/2016- 7/20/2016

    Sorry couldn't put them in a table. X are blanks and - separate columns
    Any help would be greatly appreciated!

    Thanks,

    Mike

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    Re: Array that brings a value to a set of mulptiple cells

    Try

    =IFERROR(INDEX($D$1:$D$9,INDEX(ROW($1:$9),MATCH(1,($B$1:$B$9=$B2)*(ISNUMBER($D$1:$D$9)),0))),"")


    Entered with Ctrl+Shift+Enter
    Last edited by JohnTopley; 06-09-2016 at 03:40 PM.

  3. #3
    Registered User
    Join Date
    12-24-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Thumbs up Re: Array that brings a value to a set of mulptiple cells

    Thank you John, that works!

+ 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. Multipage control brings up Toolbox !?
    By tfurnivall in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2013, 07:20 AM
  2. COUNTIF brings back too many numbers
    By jceg316 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2013, 11:50 AM
  3. Linking a number so that it brings up a name
    By wckyle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2013, 12:25 PM
  4. Calculating How Much Each Person Brings in a Month
    By Big D in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-17-2013, 11:34 AM
  5. Find and Replace All brings up Dialog Box
    By amartino44 in forum Excel General
    Replies: 2
    Last Post: 02-21-2013, 08:41 PM
  6. Insert a button that brings up a form?
    By danielgri14 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2011, 11:17 PM
  7. why does the cursor brings up multiple cells
    By hertzfl2 in forum Excel General
    Replies: 9
    Last Post: 04-18-2009, 04:04 PM
  8. NEED A FORMULA THAT BRINGS OUT NAME OF COLUMN (HEADER) A VALUE BE.
    By DAGO in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2005, 04:06 PM

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