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

[rickord]

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.

 

[jorismoerings]

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.