+ Reply to Thread
Results 1 to 4 of 4

Creating array - match, index, if and so on

  1. #1
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Creating array - match, index, if and so on

    Hi all again. Here's the problem.1 formula is needed to create array of coefficients which is the result of:

    1. Checking whose names in A and find these names in B1:G1.
    2. Only for these names find the earliest dates which are greater than the date in H1 and display the coefficients for these dates.

    With 2 for the case of 1 name (for example, John) i got great help here previously and the formula is
    "Cool Formula"=INDEX($C$2:$C$4,MATCH(SMALL(IF($B$2:$B$4>H1,$B$2:$B$4),1),$B$2:$B$4,0),1).

    That's it. if it's interesting what i thought of read below, if not, skip it:
    I thought of Index function:
    - for array - B1:G5
    - for column numbers - match(A2:A3,B1:G1)+1 , so
    - for row number - here the part of Cool formula MATCH(SMALL(IF($B$2:$B$4>H1,$B$2:$B$4),1),$B$2:$B$4,0) could define the raw, but it should be done not only for B2:B4 but for all right right columns (numbers of which could be taken from match(A2:A3,B1:G1). And at this stage i'm getting lost


    =index(B1:G5,??????,match(A2:A3,B1:G1)+2)

    A------------B---------C-------------D----------E-----------F---------------G------------H-----
    1--Customer---John--------coef---------Inga ------coef---------Alice----------sum-------2/14/2012
    2--John------1/1/2012---1.6887104--1/6/2012---1.3712521----1/3/2012-----1.040553927------------
    3--Alice------2/15/2012--0.987174---2/2/2012---0.8076068----2/4/2012------0.602202203-----------
    4------------3/25/2012--0.0797610--3/31/2012--0.116074----3/28/2012------0.575847398------------


    Thanks in advance.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Creating array - match, index, if and so on

    Hi Alexander,

    What is the result you are looking for.. i.e., If Alice is in column F, you need the maximum date which is less than 2/14/2012?


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Creating array - match, index, if and so on

    Hi, thanks for replaying. The result will be the array - {0.987174,0.575847398} - two values (for John and Alice), which are corresponding to the earliest dates greater then H1 (dates from B and F). Hope it helps.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Creating array - match, index, if and so on

    Hi..

    Why are you considering John..?
    I am confused... could you upload a sample workbook.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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