+ Reply to Thread
Results 1 to 9 of 9

Index, Match, Row, Small functions are properly not set in my formula

  1. #1
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Index, Match, Row, Small functions are properly not set in my formula

    Hi friends,

    Please see below attachments for better understanding.

    In that attachment I want multiply O6 with P1, AND
    multiply P6 with P1

    Please edit my O6, P6 formulas......

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Index, Match, Row, Small functions are properly not set in my formula

    That doesn't help to explain at all.

    Please mock up what you want in a separate workbook. It is also a lot easier with mock data where you just illustrate the problem, instead of giving the entire thing. That way there is less for us to look at and we are able to pin-point the issue.

  3. #3
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Ok, Now you can check attachment,

    Rule 1 : From N6, I want min rate to maximum rate

    Rule 2 : N6 & O6 multiply with O1
    Attached Files Attached Files
    Last edited by rajeshn_in; 11-09-2016 at 01:45 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Please explain - in words - what you are trying to do here.

    This formula seems to do the same as your longer 1...
    =SMALL(IF(($D$2:$D$5=$M$1)*($E$2:$E$5=$M$2)*($B$2:$B$5<=$M$3)*($C$2:$C$5>=$M$3),$G$2:$G$5),ROWS($N$6:N6))
    vs
    =IFERROR((SMALL(IF($D$2:$D$5=$M$1,IF($E$2:$E$5=$M$2,IF($B$2:$B$5<=$M$3,IF($C$2:$C$5>=$M$3,$G$2:$G$5+ROW($G$2:$G$5)/10^10)))),ROWS($N$6:N6))),"")
    Is this not working for you? If not, what did you want?

    Rule 2 : N6 & O6 multiply with O1
    This?
    =O6*P6*$O$1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Ok,

    =SMALL(IF(($D$2:$D$5=$M$1)*($E$2:$E$5=$M$2)*($B$2:$B$5<=$M$3)*($C$2:$C$5>=$M$3),$G$2:$G$5),ROWS($N$6:N6))

    with this formula, other cells (L6, M6, O6, P6) values are not coming.....

    =O6*P6*$O$1 MEANS

    =(SMALL(IF(($D$2:$D$5=$M$1)*($E$2:$E$5=$M$2)*($B$2:$B$5<=$M$3)*($C$2:$C$5>=$M$3),$G$2:$G$5),ROWS($N$6:N6))) * $O$1

    Same as in P6 formula * $O$1

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Explain in words what you are trying to do

  7. #7
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Actually I want smallest to highest value from N6 to N9 comparing with table A1:J5, with some IF conditions

    Thats way I try below formula in N6 (excel array )
    =(SMALL(IF(($D$2:$D$5=$M$1)*($E$2:$E$5=$M$2)*($B$2:$B$5<=$M$3)*($C$2:$C$5>=$M$3),$G$2:$G$5),ROWS($N$6:N6)))

    After that using another formula I get values in L6, M6, O6, P6 according to N6 (excel array )
    =IFERROR(INDEX($A$2:$A$5,MATCH(N6,$G$2:$G$5+ROW($G$2:$G$5)/10^10,0)),"")

    and after that I want multiply N6 with O1,

    After that I want to multiply O6 with O1.

    But problem is, when I am trying to multiply N6, then L6, M6, O6, P6 values are not coming
    Last edited by rajeshn_in; 11-09-2016 at 03:57 AM.

  8. #8
    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
    27,999

    Re: Index, Match, Row, Small functions are properly not set in my formula

    O6

    =iferror((index(($h$2:$h$5)+($i$2:$i$5),match(n6,$g$2:$g$5+row($g$2:$g$5)/10^10,0)))*$o$1,"")

    p6

    =iferror((index(($j$2:$j$5),match(n6,$g$2:$g$5+row($g$2:$g$5)/10^10,0)))*$o$1,"")

  9. #9
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Index, Match, Row, Small functions are properly not set in my formula

    Ok, its working great...

    N6 = ?
    L6 = ?
    M6 = ?

+ 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. [SOLVED] Help Requested with Nested Small Function Inside Index/Match Formula
    By trandle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 10:07 AM
  2. Excel Formula: IFERROR, INDEX, SMALL, MATCH
    By Jenn12788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 10:03 PM
  3. [SOLVED] Formula using INDEX, SMALL, and ISERROR functions gives skewed results
    By rspells in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-09-2013, 03:32 AM
  4. Can't find the error functions INDEX () and SMALL()
    By HyperG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2012, 12:20 AM
  5. Large, Small, Index, Match formula combos
    By taudano in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 01:53 AM
  6. Replies: 2
    Last Post: 01-20-2011, 01:21 PM
  7. INDEX and MATCH functions...formula audit please
    By jim_0068 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2007, 05:48 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