+ Reply to Thread
Results 1 to 5 of 5

match and give the value

  1. #1
    Registered User
    Join Date
    05-29-2014
    Posts
    20

    match and give the value

    i have attached an excel file , in that i want the names for the particular date where value corresponding to the particular date is greater than 4.

    For examples , i should get names :x,q,s for date 22/02/16
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: match and give the value

    Hi Gaurav
    In A13 type date 22/02/16 in B13 type 4 then in A14 copy paste below then hold control and shift together and then hit enter to make it array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down


    array formulas are ...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.
    Attached Files Attached Files
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    05-29-2014
    Posts
    20

    Re: match and give the value

    CAN YOU PLEASE EXPLAIN
    how in this formula, second last ROW function and last ROWS function is working
    means how it is executing steps.
    actually i have to do that in large data , i should know how this happens

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: match and give the value

    Would this work for you?
    Enter where you want the results. I copied the dates into another area and entered this formula under the first date and filled across and down to give the names that had values >4 for each date.
    Enter this ARRAY formula (Ctrl + Shift + Enter)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The absolute reference to the range in column A assures that the reference to column A remains constant when the formula is filled across and down.
    INDEX is the range that contains the values to be returned by the rest of the formula.
    SMALL has IF to define the range of values to be considered. The IF statement IF(B$3:B$10>4 will only consider values in B3:B10 that are greater than 4. The TRUE part of the IF statement ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1 gives the values {1;2;3;4;5;6;7;8} which are the rows being evaluated for being greater than 4. When taken as a complete statement IF(B$3:B$10>4,ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1) is understood by the SMALL function that the IF is in as {FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE}. All the FALSEs are the rows that have values that are <4. The numbers are the rows that have values >4.

    ROWS($1:1) is the K factor for the SMALL function and will increase by 1 for each row filled down. This means that the smallest numbered row with a value >4 will be returned, the second row will have the next smallest row value containing a value >4 etc.

    The whole formula is surrounded by and IFERROR which eliminates showing error values where there isn't data to be returned.

    I
    J
    K
    L
    M
    N
    2
    22/02/2016
    23/02/2016
    24/02/2016
    25/02/2016
    26/02/2016
    27/02/2016
    3
    x
    z
    x
    z
    v
    z
    4
    q
    x
    d
    x
    d
    x
    5
    s
    d
    q
    v
    q
    s
    6
    7
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: match and give the value

    @ gaurav
    =IFERROR(INDEX($A$3:$A$10,SMALL(IF(INDEX($B$3:$G$10,0,MATCH($A$13,$B$2:$G$2,0))>$B$13,ROW($A$3:$A$10)-ROW($A$3)+1),ROWS($A$1:A1))),"")

    Bold part is first construction that brings all numbers in form of array for matched date, then those array of numbers are checked whether greater than number available in B13 or not, if greater then for that number, Row number is returned and those which are not False is returned.

    here the construction Row($A$3:$A$10)-Row($A$3)+1 gives the serial numbers from if function as below
    {FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE} as for attached file these are the row numbers where criteria is matched

    Now small function picks 1 st smallest value from array and gives 3 which becomes row number for index function

    Rows($A$1:A1) generates serial numbers as 1,2,3,4,5,6 and so on as you drag your formula down that becomes nth factor for small function

    For further insight click the cell with formula, go to formula tab and hit formula evaluation to check how things are taking place.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Match up function to find the value and give name
    By Jung Bin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2015, 06:55 AM
  2. [SOLVED] Formula to match 2 tabs to give 1 result.
    By sevanseriesta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 03:15 PM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. [SOLVED] Match Column And Row to give value, index and match?
    By zhunter71 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-24-2013, 01:05 PM
  5. seach and match to give result
    By prasjohn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2009, 06:19 AM
  6. match 2 colomns to give result
    By semo in forum Excel General
    Replies: 2
    Last Post: 05-28-2009, 06:31 AM
  7. Match one cell for another AND give results
    By krayziez in forum Excel General
    Replies: 4
    Last Post: 06-03-2006, 10:25 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