Results 1 to 2 of 2

Adding additional 'IF' statements into a dynamic list formula (Pt:2)

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Adding additional 'IF' statements into a dynamic list formula (Pt:2)

    I had some help with the origional formulae here - please see thread for sample workbook etc.

    So I have the formula:
    =IFERROR(INDEX(Sheet2!A$2:A$1000,SMALL(IF(Sheet2!$Q$2:$Q$1000="UN",ROW(Sheet2!A$2:A$1000)-1),ROW(Sheet2!A1))),"")
    which I managed to modify for another use on another tab (on my main file, it bears no references to the sample workbook uploaded) to this:
    =IFERROR(INDEX('Sheet2'!AY$2:AY$1000,SMALL(IF('Sheet2'!$AX$2:$AX$1000=$B$3,ROW('Sheet2'!AY$2:AY$1000)-1),ROW('Sheet2'!AY1))),"")
    This basically checks if any date in the column AX matches the date in B3, and pulls out those rows.

    My new problem, is that I want to add in an additional 'IF' statement, so that as well as checking if the date matches in AX, I want it to check column L and see if data matches a specific text string.

    I though just adding an IF(AND(... bit in would work, but it doesn't, so I'm completely stumped...

    What I thought I needed:
    =IFERROR(INDEX('Sheet2'!AY$2:AY$1000,SMALL(IF(AND('Sheet2'!$AX$2:$AX$1000=$B$3,'Sheet2'!$L$2:$L$1000="chq"),ROW('Sheet2'!AY$2:AY$1000)-1),ROW('Sheet2'!AY1))),"")
    Can someone tell me why this doesn't work, and help me achieve what I'm after?

    Many thanks in advance.
    x
    Last edited by dancing-shadow; 10-14-2012 at 04:57 AM.

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