Results 1 to 2 of 2

Thread: Return result for 1st, 2nd, and 3rd Row

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return result for 1st, 2nd, and 3rd Row

    Hi Everyone,

    I'm a bit stumped on a formula I'm building. I am trying to return the close date for the 1st, 2nd, and 3rd Sales deal for all of my sales reps. I built out the following formula, which works if the sales rep has actually closed 3 deals. However, if the sales rep only closed 1 or 2 deals, the data defaults to a random date. Any thoughts on how to fix?

    1st result =iferror(index('Deal Data'!$F:$F,match($B2,'Deal Data'!$C:$C,false),1),"-")
    2nd result
    =iferror(index('Deal Data'!$F:$F,match($B2,'Deal Data'!$C:$C,false)+1,1),"-")
    3rd result
    =iferror(index('Deal Data'!$F:$F,match($B2,'Deal Data'!$C:$C,false)+2,1),"-")

    The data comprises of the following.. on the cover sheet, Reps Name(Col A), 1st Deal(Col B), 2nd Deal(Col C) and 3rd deal(Col D). My lookup data is in another tab "Deal Data" and indexes all of the dates in column F and matches to Sales Rep name.

    Any help would be great, I'm thinking I need to write an IF statement to stop the result.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Return result for 1st, 2nd, and 3rd Row

    Hi,

    In the way you've entered the formula it will not default the data but it will just continue to the next salesrep.
    This can be solved to limit the Index source just to the Salesrep you're looking for.

    something like this assuming the names of the salesrep is in column C.

    1st result =iferror(index(if(Deal Data'!$C:$C = $b2,Deal Data'!$F:$F,""); match($B2,'Deal Data'!$C:$C,false),1),"-")
    2nd result
    =iferror(index(if(Deal Data'!$C:$C = $b2, Deal Data'!$F:$F,"");,match($B2,'Deal Data'!$C:$C,false)+1,1),"-")
    3rd result
    =iferror(index(if(Deal Data'!$C:$C = $b2, Deal Data'!$F:$F,"");match($B2,'Deal Data'!$C:$C,false)+2,1),"-")


    And as these formula's are array based formula you should confirm the with the key combination Ctrl+Shift+Enter.
    If confirmed correctly Excel will start and close the formula with {}

    Just as a note: because the array formula usually takes extra calculation time it's advised to limit your columns to the number of row instead of looking at a million rows.

    Hope this helps.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •