IF with Unless conditions?

[Koleyd]

Hi, Hoping I can get some assistance on this. So I'm pretty much a novice excel user when it comes to formulas and logics; but I am trying to somewhat automate a commission report we use with a few different formulas for requirements.
There are a few conditions that need to be met and different results to be returned.
We have a Specific Customer set value; a Standard Item value and a few Specific Item values.
What I need to do is:
If Customer # H= 'this' then return "$ value" for all entries (eg.7.00); UNLESS the Item # Q= 'this or this' (the vlookup of the formula) then return value from column 2; BUT if neither is true then return Standard Item Value '$10.00'.
However, I literally do not know enough about excel to make this work as a formula and no amount of googling has rendered a result that works either .
I assume that it can be done?
Below is what I have been working with and it kind of works. But it only returns the Customer value because it is true; then ignores the rest of the requirements. Help!
EXAMPLE ONLY =IFERROR(IF(H11="A101042AUD","7.00",(VLOOKUP(Q11,Sheet2!$F$2:$G$4,2,FALSE))),10)

Please tell me this can be done? And thanks in advance any assistance would be greatly appreciated.

 

[earthworm]

Share data in excel please

 

[Koleyd]

I hope this works!

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	CUST #	NAME			DATE	SALES	AGENT			ITEM #		PRODUCT		catergory	location	MT	UNIT	EXT PR	COM RATE	COM DUE
2	A101539	STAN			8/2/2021	SALES	AGENT			FA001		APPLES		catergory	location	49.99	527.00	26 344.73	5.00	249.95
3	A101042	JOE			8/4/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	29.00	505.00	14 645.00	5.00	145.00
4	A101042	JOE			8/4/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	31.05	505.00	15 680.25	5.00	155.25
5	A101539	STAN			8/4/2021	SALES	AGENT			FA001		APPLES		catergory	location	50.03	527.00	26 365.81	5.00	250.15
6	A101410	ERIC			8/5/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	628.00	12 560.00	5.00	100.00
7	A102470	PAUL			8/5/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	588.00	31 763.76	5.00	270.10
8	A101042	JOE			8/9/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.05	505.00	15 175.25	5.00	150.25
9	A101042	JOE			8/9/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.00	600.00	18 000.00	5.00	150.00
10	A102470	PAUL			8/7/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	588.00	31 763.76	5.00	270.10
11	A102528	MICHAEL			8/10/2021	SALES	AGENT			FM190		PEACHES		catergory	location	10.00	628.00	6 280.00	10.00	100.00
12	A101410	ERIC			8/14/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	628.00	12 560.00	5.00	100.00
13	A101042	JOE			8/13/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	53.75	505.00	27 143.75	5.00	268.75
14	A101042	JOE			8/11/2021	SALES	AGENT	MIKES		FA001		APPLES		catergory	location	54.00	598.00	32 292.00	5.00	270.00
15	A101756	DAVE			8/18/2021	SALES	AGENT			FN0036		AVOCADOS		catergory	location	16.00	493.00	7 888.00	10.00	160.00
16	A101042	JOE			8/19/2021	SALES	AGENT	MIKES		FF062		PLUMS		catergory	location	25.00	484.00	12 100.00	10.00	250.00
Sheet1

Cell Formulas
Range	Formula
Z2:Z16	Z2	=IFERROR(IF(H2="A101042AUD","7.00",(VLOOKUP(Q2,Sheet2!$F$2:$G$4,2,FALSE))),10)
AA2:AA16	AA2	=SUM(Z2*W2)

Book1
	D	E	F	G
1
2			COMMISSION RATE TABLE
3			FM001	5
4			FA001	5
5		EVERYTHING ELSE		10
6
7
8			CUSTOMER TABLE
9			A101042	5
10			A101552	7
11
Sheet2

 

[earthworm]

pLEAS

I hope this works!

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	CUST #	NAME			DATE	SALES	AGENT			ITEM #		PRODUCT		catergory	location	MT	UNIT	EXT PR	COM RATE	COM DUE
2	A101539	STAN			8/2/2021	SALES	AGENT			FA001		APPLES		catergory	location	49.99	527.00	26 344.73	5.00	249.95
3	A101042	JOE			8/4/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	29.00	505.00	14 645.00	5.00	145.00
4	A101042	JOE			8/4/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	31.05	505.00	15 680.25	5.00	155.25
5	A101539	STAN			8/4/2021	SALES	AGENT			FA001		APPLES		catergory	location	50.03	527.00	26 365.81	5.00	250.15
6	A101410	ERIC			8/5/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	628.00	12 560.00	5.00	100.00
7	A102470	PAUL			8/5/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	588.00	31 763.76	5.00	270.10
8	A101042	JOE			8/9/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.05	505.00	15 175.25	5.00	150.25
9	A101042	JOE			8/9/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.00	600.00	18 000.00	5.00	150.00
10	A102470	PAUL			8/7/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	588.00	31 763.76	5.00	270.10
11	A102528	MICHAEL			8/10/2021	SALES	AGENT			FM190		PEACHES		catergory	location	10.00	628.00	6 280.00	10.00	100.00
12	A101410	ERIC			8/14/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	628.00	12 560.00	5.00	100.00
13	A101042	JOE			8/13/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	53.75	505.00	27 143.75	5.00	268.75
14	A101042	JOE			8/11/2021	SALES	AGENT	MIKES		FA001		APPLES		catergory	location	54.00	598.00	32 292.00	5.00	270.00
15	A101756	DAVE			8/18/2021	SALES	AGENT			FN0036		AVOCADOS		catergory	location	16.00	493.00	7 888.00	10.00	160.00
16	A101042	JOE			8/19/2021	SALES	AGENT	MIKES		FF062		PLUMS		catergory	location	25.00	484.00	12 100.00	10.00	250.00
Sheet1

Cell Formulas
Range	Formula
Z2:Z16	Z2	=IFERROR(IF(H2="A101042AUD","7.00",(VLOOKUP(Q2,Sheet2!$F$2:$G$4,2,FALSE))),10)
AA2:AA16	AA2	=SUM(Z2*W2)

Book1
	D	E	F	G
1
2			COMMISSION RATE TABLE
3			FM001	5
4			FA001	5
5		EVERYTHING ELSE		10
6
7
8			CUSTOMER TABLE
9			A101042	5
10			A101552	7
11
Sheet2

Please explain this piece
But it only returns the Customer value because it is true; then ignores the rest of the requirements. Help!

The formula seems correct what else are you expecting.

 

[Koleyd]

pLEAS

Please explain this piece
But it only returns the Customer value because it is true; then ignores the rest of the requirements. Help!

The formula seems correct what else are you expecting.

Apologies, I think I have not included the altered formula to work with the example information. Please see this one. Note the highlighted line. The Customer Number is true in the formula; so the $7 value is returned; however the Item # is also one of the requirements that should take precedence over the Customer number and return a value of $5. Does that make sense?

If customer is A101042 then return $7 UNLESS the item number is FM001 (from the table) then return $5; irrespective of the customer number being true.

Also to add to this, I would like the ability to search the Customer Table for the customer numbers and associated rate, rather than having a static value placed inside the formula as it currently stands.

com report test.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	CUST #	NAME			DATE	SALES	AGENT			ITEM #		PRODUCT		catergory	location	MT	UNIT	EXT PR	COM RATE	COM DUE
2	A101539	STAN			02/08/2021	SALES	AGENT			FA001		APPLES		catergory	location	49.99	500.00	24 995.00	5.00	249.95
3	A101042	JOE			04/08/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	29.00	500.00	14 500.00	7.00	203.00
4	A101042	JOE			04/08/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	31.05	500.00	15 525.00	7.00	217.35
5	A101539	STAN			04/08/2021	SALES	AGENT			FA001		APPLES		catergory	location	50.03	500.00	25 015.00	5.00	250.15
6	A101410	ERIC			05/08/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	500.00	10 000.00	5.00	100.00
7	A102470	PAUL			05/08/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	500.00	27 010.00	5.00	270.10
8	A101042	JOE			09/08/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.05	500.00	15 025.00	7.00	210.35
9	A101042	JOE			09/08/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.00	500.00	15 000.00	7.00	210.00
10	A102470	PAUL			07/08/2021	SALES	AGENT			FA001		APPLES		catergory	location	54.02	500.00	27 010.00	5.00	270.10
11	A102528	MICHAEL			10/08/2021	SALES	AGENT			FM190		PEACHES		catergory	location	10.00	600.00	6 000.00	10.00	100.00
12	A101410	ERIC			14/08/2021	SALES	AGENT			FM001		ORANGES		catergory	location	20.00	500.00	10 000.00	5.00	100.00
13	A101042	JOE			13/08/2021	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	53.75	500.00	26 875.00	7.00	376.25
14	A101042	JOE			11/08/2021	SALES	AGENT	MIKES		FA001		APPLES		catergory	location	54.00	500.00	27 000.00	7.00	378.00
15	A101756	DAVE			18/08/2021	SALES	AGENT			FN0036		AVOCADOS		catergory	location	16.00	700.00	11 200.00	10.00	160.00
16	A101042	JOE			19/08/2021	SALES	AGENT	MIKES		FF062		PLUMS		catergory	location	25.00	500.00	12 500.00	7.00	175.00
17
Sheet1

Cell Formulas
Range	Formula
Y2:Y16	Y2	=X2*W2
Z2:Z16	Z2	=IFERROR(IF(H2="A101042","7.00",(VLOOKUP(Q2,Sheet2!$F$2:$G$4,2,FALSE))),10)
AA2:AA16	AA2	=SUM(Z2*W2)

com report test.xlsx
	F	G
1
2	COMMISSION RATE TABLE
3	FM001	5
4	FA001	5
5	EVERYTHING ELSE	10
6
7
8	CUSTOMER TABLE
9	A101042	7
10	A101552	5
Sheet2

 

[earthworm]

Try now . I have intentionally Kept Zero where customer doesn't exist if you still want 10 to appear then apply below formula

=IF(A2="","",IFERROR(IF(NOT(ISERROR(VLOOKUP(J2,$V$3:$W$4,2,0))),5,VLOOKUP(A2,$V$9:$W$10,2,0)),10))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	CUST #	NAME			DATE	SALES	AGENT			ITEM #		PRODUCT		catergory	location	MT	UNIT	EXT PR	COM RATE	COM DUE
2	A101539	STAN			2-Aug-21	SALES	AGENT			FA001		APPLES		catergory	location	49.99	500	0	5	0		COMMISSION RATE TABLE
3	A101042	JOE			4-Aug-21	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	29	500	0	5	0		FM001	5
4	A101042	JOE			4-Aug-21	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	31.05	500	0	5	0		FA001	5
5	A101539	STAN			4-Aug-21	SALES	AGENT			FA001		APPLES		catergory	location	50.03	500	0	5	0		EVERYTHING ELSE	10
6	A101410	ERIC			5-Aug-21	SALES	AGENT			FM001		ORANGES		catergory	location	20	500	0	5	0
7	A102470	PAUL			5-Aug-21	SALES	AGENT			FA001		APPLES		catergory	location	54.02	500	0	5	0
8	A101042	JOE			9-Aug-21	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30.05	500	0	5	0		CUSTOMER TABLE
9	A101042	JOE			9-Aug-21	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	30	500	0	5	0		A101042	7
10	A102470	PAUL			7-Aug-21	SALES	AGENT			FA001		APPLES		catergory	location	54.02	500	0	5	0		A101552	5
11	A102528	MICHAEL			10-Aug-21	SALES	AGENT			FM190		PEACHES		catergory	location	10	600	0	0	0
12	A101410	ERIC			14-Aug-21	SALES	AGENT			FM001		ORANGES		catergory	location	20	500	0	5	0
13	A101042	JOE			13-Aug-21	SALES	AGENT	MIKES		FM001		ORANGES		catergory	location	53.75	500	0	5	0
14	A101042	JOE			11-Aug-21	SALES	AGENT	MIKES		FA001		APPLES		catergory	location	54	500	0	5	0
15	A101756	DAVE			18-Aug-21	SALES	AGENT			FN0036		AVOCADOS		catergory	location	16	700	0	0	0
16	A101042	JOE			19-Aug-21	SALES	AGENT	MIKES		FF062		PLUMS		catergory	location	25	500	0	7	0
Sheet1

Cell Formulas
Range	Formula
R2:R16	R2	=X2*W2
S2:S16	S2	=IF(A2="","",IFERROR(IF(NOT(ISERROR(VLOOKUP(J2,$V$3:$W$4,2,0))),5,VLOOKUP(A2,$V$9:$W$10,2,0)),0))
T2:T16	T2	=SUM(Z2*W2)

 

[Koleyd]

Try now . I have intentionally Kept Zero where customer doesn't exist if you still want 10 to appear then apply below formula

=IF(A2="","",IFERROR(IF(NOT(ISERROR(VLOOKUP(J2,$V$3:$W$4,2,0))),5,VLOOKUP(A2,$V$9:$W$10,2,0)),10))

OMG, thanks so much! This works perfectly; and I can expand on item numbers & customers!! You're a Champion!! ?