+ Reply to Thread
Results 1 to 6 of 6

If, INDEX, ROWS Function

  1. #1
    Registered User
    Join Date
    08-15-2018
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    If, INDEX, ROWS Function

    I am trying to find all of the Imprint Orders on December18 and create a list on Sheet2 using the array formula
    =IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,ROW(December18!$D$1:$DX$5)-ROW(December18!$D$1)+1),ROWS(A$3:A3))),””)

    I get 15 results but they are all of the first order which is not marked "Imprint".
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: If, INDEX, ROWS Function

    Looks like you have rows and columns confused, try this instead

    =IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,COLUMN(December18!$D$1:$DX$1)-ROW(December18!$D$1)+1),ROWS(A$3:A3))),"")

  3. #3
    Registered User
    Join Date
    08-15-2018
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: If, INDEX, ROWS Function

    That kind of worked. I returned 16 records but only 5 of them are correct. The other 11 are not marked "imprint".
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: If, INDEX, ROWS Function

    Formulas in A4, B4, C4 then dragged down.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: If, INDEX, ROWS Function

    Sorry, there were 2 row references thta should have been changed to column, I thought that I had done both, but missed the second one.

    =IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,COLUMN(December18!$D$1:$DX$1)-COLUMN(December18!$D$1)+1),ROWS(A$3:A3))),"")

  6. #6
    Registered User
    Join Date
    08-15-2018
    Location
    United States
    MS-Off Ver
    2010
    Posts
    3

    Re: If, INDEX, ROWS Function

    That worked!! Thank you so much!

+ 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. Index Match Function? Ignore blank rows - (rows without values)
    By jgray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2015, 01:12 PM
  2. Index and Match function across multiple rows with repeating names
    By FKOC in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-09-2014, 11:17 PM
  3. Need help with index function - combining rows.
    By psgolfer32381 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2013, 04:01 PM
  4. Replies: 3
    Last Post: 06-18-2012, 06:07 AM
  5. combining the index and columns/rows function
    By brucezepplin in forum Excel General
    Replies: 4
    Last Post: 03-23-2012, 12:24 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