+ Reply to Thread
Results 1 to 8 of 8

small and index formila not working correctly

  1. #1
    Forum Contributor
    Join Date
    12-23-2007
    Location
    chennai india
    MS-Off Ver
    2010
    Posts
    114

    small and index formila not working correctly

    I am using small and index formula. Sample file is attached. In sheet 1 the results are not coming, in sheet2 only 3 rows appear and after 7 again the 1st row reappears. I get this mistake often. I would be thankful to you all if error is corrected and the mistake and logic behind the error.
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: small and index formila not working correctly

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

    =IFERROR(INDEX('Sales Register'!$A$1:$V$424,SMALL(IF('Sales Register'!$C$1:$C$424=Sheet1!$B$17,ROW('Sales Register'!$C$1:$C$424),""),'Sales Register'!A2-1),COLUMN('Sales Register'!V1)),"")

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: small and index formila not working correctly

    Hi -

    I think the formulas are working correctly. Your sample data is at least part of the problem. For example, on Sheet2, cell A24, where nothing shows up, the formula is taking the smallest of either the ROW where it finds ES/16-17/01878, which is the first row it occurs on the Sales Register Sheet at C38 which is 38 OR the contents of cell 'Sales Register'!$A31-2. The contents of 'Sales Register'!$A31 is 2. 2-2=0. So the smaller of 38 or 0 is 0. By definition, there is no row 0 so you get an error. Since your formula has an IFERROR function, it returns a "". Which is why nothing shows up there.

    Another example: Sheet2 Cell A29: The result that shows up is 1. This is the same problem as above. We are comparing Row 38 to the contents of cell 'Sales Register'!$A36-2. That cell contains the number 3. 3-2=1. The smaller of 38 and 1 is 1. Therefore you are getting what is stored in Row 1.

    I'm not entirely sure what you are doing with these formulas, so I can't really offer advice on the overall formulas. However, I think your sample data is the first part of the problem.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  4. #4
    Forum Contributor
    Join Date
    12-23-2007
    Location
    chennai india
    MS-Off Ver
    2010
    Posts
    114

    Re: small and index formila not working correctly

    Very much thanks for your effort and reply.Infact sheet sales register column A is helper column. I can remove it. Can you suggest me how to rectify and improve it.

    I would value your effort.

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: small and index formila not working correctly

    Hi -

    You will have to explain to me what you are trying to do with this spreadsheet.

  6. #6
    Forum Contributor
    Join Date
    12-23-2007
    Location
    chennai india
    MS-Off Ver
    2010
    Posts
    114

    Re: small and index formila not working correctly

    sorry in the middle i submiited thread. In column c heading voucher no. I want the details of (sheet 1 or2 B17) it in sheet 1 or 2 (from a21) i wanted result from L column. same from f21 results from v column. This formula i have taken from this forum and in some of workshhet it is working fine.

  7. #7
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: small and index formila not working correctly

    Try in Sheet2 F21 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,216

    Re: small and index formila not working correctly

    In Sheet1 F21 I changed the formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It's still array entered.

    One of the changes I made was this 'Sales Register'!C1:$C$424 to this 'Sales Register'!$C$1:$C$424

    Another is that to change the K argument of SMALL to ROWS($21:21). It's simpler. Note: the formula starts in row 21.

    Another is to use the MATCH function to return the column argument of INDEX. Note: the spelling of "Acutal Quantity" must exactly match the header spelling ... with no leading or trailing spaces.
    Dave

+ 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 Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  2. index & match formula is not working correctly
    By integra in forum Excel General
    Replies: 2
    Last Post: 09-15-2015, 08:30 PM
  3. [SOLVED] Struggling to get a small search function working correctly
    By bodhi808 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2013, 08:34 PM
  4. [SOLVED] Basic question about inserting a date into a formila
    By DWG3 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-25-2013, 03:26 PM
  5. Replies: 9
    Last Post: 03-08-2013, 11:50 AM
  6. Using IF Function
    By RZ100 in forum Excel General
    Replies: 4
    Last Post: 04-02-2012, 08:49 AM
  7. Issues With Index/Match Not Working Correctly.
    By mgraesch in forum Excel General
    Replies: 2
    Last Post: 07-31-2009, 10:33 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