Excel 2007 : Lookup, Vlookup, or IF

Good Morning!

Been poking around trying to find a solution, but..... I have a report that I've created (attached) which shows net sales for a given month, with a Region Manager, Territory Manager, and Sales Rep. Each manager, terriroty and rep is assigned a number.

Commission is paid on net sales to each sales rep on the report, EXCEPT those accounts where a sales manager is the sales rep.

In those instances, the commission rate should be 0.

I'm trying to have a formula/function look in column G, determine whether or not it's a rep or a manager, then either keep the rate or make it zero.

I know I could use the Lookup Function, but can I nest it in an IF statement?

This may be a round about way to do this, but in order to create this report to begin with, there's 4 seperate tables, many vlookups and sumifs functions to go thru first.

Anyone have any ideas?

Thanks as always...

I think you meant look in column D, correct?

Try:

=IF(ISNUMBER(SEARCH(","&D3&",",","&$L$6&",")),0,J3)

copied down

Hi Rick,

Put this formula in K3 and pull it down.

Please Login or Register  to view this content.