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

rickord

New Member
Joined
May 7, 2015
Messages
2
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top