Vlookup based on multiple criteria/conditions

[narendra]

Is there a way to do a lookup with multiple conditions where one or more conditions are checked against different lookup values, and where it returns an error or a string value if any of the condition fails (i.e. a lookup value is Not found or, does Not fall within the checked range)?
For example, in below sheet, i want to get Rate(%) and Discount-Code from the row where the following 3 checks are True:
a) match the Customer-code
b) match the Region
c) where Transaction Date falls within the 'Eff From' and 'Eff upto' Date Range --Note: the 'Eff upto' date will be blank/empty for a current open period until the Rate or Discount-code is changed.
For getting Rates, I can use an Array Formula with Sum/Sumproduct (an example using SUM is given in below table). BUT this does not work for getting the Discount-Codes.

The desired results are colored in Red in the Right-side (Destination) section. See the top 6 results to get an idea.
In the 6th result one of the conditions failed (the Transaction Date falls before the periods available for that customer-code and region). Here, because of the Sum function, the array formula returns 0 for the Rate instead of an error. This can be misleading as some zones are assigned 0% Rates as well.
So,
a) for the rates, how can i return an error or a string value instead of 0 when any of the condition fails ? and
b) how to get the correct discount-codes while returning and error or string value when any of the condition fails ?

Do i need to use any other lookup function to get the desired results?

Excel-2020_lookup-multiple-conditions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Source Data :								Destination :
2		Rate/Disc Master								Transaction List
3										(lookups reqd. here)				Needs a better	Needs a	Lookup
4		Condition 1	Condition 2	Get this	Get this	Condition 3	Condition 3							Lookup Formula	Lookup Formula	Cust Code
5		↓	↓	↓	↓	↓	↓							↓	↓	↓
6	Name	Customer Code	Region	Rate	Disc Code	Eff From	Eff upto			Customer Code	Region	Transaction Date	Amt	Effective Rate	Disc Code	Name
7	John	JC005	North	12%	Q2	1-Jan-2015	18-Aug-2018	<--1		JC005	North	15-Jul-2019	17650	16.75%	M2	John	*from 2
8	John	JC005	West	10.25%		2-Nov-2014	15-Sep-2019			JC005	North	12-Feb-2017	18200	12%	Q2	John	*from 1
9	Amy	AL162	North	5.6%	ACT	24-May-2018	14-Jan-2020			JC005	North	7-Feb-2020	6550	0%	Q1	John	*from 3
10	John	JC005	North	16.75%	M2	19-Aug-2018	4-Jan-2020	<--2		JC005	North	28-Oct-2020	14350	15%	M1	John	*from 4
11	John	JC005	West	14.1%	Q5	16-Sep-2019				JC005	North	28-Dec-2020	10700	15%	M1	John	*from 4
12	John	JC005	North	0%	Q1	5-Jan-2020	7-Mar-2020	<--3		JC005	North	1-Nov-2014	5650	0%		John	out of range
13	Brian	BK038	South	18%	ACT	1-Jan-2016											↑ here in the
14	Amy	AL162	North	7%	M3	15-Jan-2020				JC005	West	12-Oct-2020	13300	14.1%		John	last entry
15	John	JC005	North	15%	M1	8-Mar-2020		<--4		JC005	West	6-Sep-2018	17400	10.25%		John	both the
16	Amy	AL162	East	11.28%		1-Jun-2018											Rate &
17					↑		↑			BK038	South	5-May-2016	18800	18%		Brian	Disc code
18					Note:		Note: for the			BK038	South	30-Oct-2020	14650	18%		Brian	formula
19					return		current(open)										should
20					empty		period the			AL162	East	1-Sep-2020	8650	11.28%		Amy	return
21					where		Eff upto' date			AL162	North	1-Jan-2020	16650	5.6%		Amy	an error Or
22					empty		is left blank.			AL162	East	24-Dec-2021	8300	11.28%		Amy	a text msg
23										AL162	North	18-Jan-2020	5650	7%		Amy
24
Sheet1

Cell Formulas
Range	Formula
N20:N23,N17:N18,N14:N15,N10:N12,N7:N8	N7	=SUM((J7=B$7:B$16)*(K7=C$7:C$16)*(L7>=F$7:F$16)*(L7<=IF(((TRIM(G$7:G$16)="")+(G$7:G$16=0)+(ISTEXT(G$7:G$16)))>0,999999999,G$7:G$16))*D$7:D$16)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Source Data :								Destination :
2		Rate/Disc Master								Transaction List
3										(lookups reqd. here)				Needs a better	Needs a	Lookup
4		Condition 1	Condition 2	Get this	Get this	Condition 3	Condition 3							Lookup Formula	Lookup Formula	Cust Code
5		↓	↓	↓	↓	↓	↓							↓	↓	↓
6	Name	Customer Code	Region	Rate	Disc Code	Eff From	Eff upto			Customer Code	Region	Transaction Date	Amt	Effective Rate	Disc Code	Name
7	John	JC005	North	12.00%	Q2	January 1, 2015	August 18, 2018	<--1		JC005	North	July 15, 2019	17650	16.75%	M2	John	*from 2
8	John	JC005	West	10.25%		November 2, 2014	September 15, 2019			JC005	North	February 12, 2017	18200	12.00%	Q2	John	*from 1
9	Amy	AL162	North	5.60%	ACT	May 24, 2018	January 14, 2020			JC005	North	February 7, 2020	6550	0.00%	Q1	John	*from 3
10	John	JC005	North	16.75%	M2	August 19, 2018	January 4, 2020	<--2		JC005	North	October 28, 2020	14350	15.00%	M1	John	*from 4
11	John	JC005	West	14.10%	Q5	September 16, 2019				JC005	North	December 28, 2020	10700	15.00%	M1	John	*from 4
12	John	JC005	North	0.00%	Q1	January 5, 2020	March 7, 2020	<--3		JC005	North	November 1, 2014	5650			John	out of range
13	Brian	BK038	South	18.00%	ACT	January 1, 2016											↑ here in the
14	Amy	AL162	North	7.00%	M3	January 15, 2020				JC005	West	October 12, 2020	13300	14.10%	Q5	John	last entry
15	John	JC005	North	15.00%	M1	March 8, 2020		<--4		JC005	West	September 6, 2018	17400	10.25%	0	John	both the
16	Amy	AL162	East	11.28%		June 1, 2018											Rate &
17					↑		↑			BK038	South	May 5, 2016	18800	18.00%	ACT	Brian	Disc code
18					Note:		Note: for the			BK038	South	October 30, 2020	14650	18.00%	ACT	Brian	formula
19					return		current(open)										should
20					empty		period the			AL162	East	September 1, 2020	8650	11.28%	0	Amy	return
21					where		Eff upto' date			AL162	North	January 1, 2020	16650	5.60%	ACT	Amy	an error Or
22					empty		is left blank.			AL162	East	December 24, 2021	8300	11.28%	0	Amy	a text msg
23										AL162	North	January 18, 2020	5650	7.00%	M3	Amy
Sheet1

Cell Formulas
Range	Formula
N14:O23,N7:O12	N7	=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$7:D$16)/($B$7:$B$16=$J7)/($C$7:$C$16=$K7)/($F$7:$F$16<=$L7)/(($G$7:$G$16>=$L7)+($G$7:$G$16="")),1)),"")

 

[narendra]

Thanks for the help Eric, it mostly works....just one thing needs to be fixed. Can we return a nullstring "" if the lookup cells are empty?

Otherwise, your formula works perfectly if the rate and discount-code cells are blank but not empty.

 

[Eric W]

That would require a minor change to the O7 formula, but you need to leave the N7 formula alone.

Book1
	N	O
1
2
3	Needs a better	Needs a
4	Lookup Formula	Lookup Formula
5	↓	↓
6	Effective Rate	Disc Code
7	16.75%	M2
8	12.00%	Q2
9	0.00%	Q1
10	15.00%	M1
11	15.00%	M1
12
13
14	14.10%	Q5
15	10.25%
16
17	18.00%	ACT
18	18.00%	ACT
19
20	11.28%
21	5.60%	ACT
22	11.28%
23	7.00%	M3
Sheet1

Cell Formulas
Range	Formula
N7:N23	N7	=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$7:D$16)/($B$7:$B$16=$J7)/($C$7:$C$16=$K7)/($F$7:$F$16<=$L7)/(($G$7:$G$16>=$L7)+($G$7:$G$16="")),1)),"")
O7:O23	O7	=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW(E$7:E$16)/($B$7:$B$16=$J7)/($C$7:$C$16=$K7)/($F$7:$F$16<=$L7)/(($G$7:$G$16>=$L7)+($G$7:$G$16="")),1))&"","")

 

[narendra]

Thank you Eric!
I also fixed the Rate(%) column to return a Blank for empty cells (before the IFERROR check I added a nullstring "" to the result and then forced convert to numeric with -- )
Below is the final sheet.

Excel-2020_lookup-multiple-conditions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Source Data :								Destination :
2		Rate/Disc Master								Transaction List
3										(lookups reqd. here)				Needs a better		Lookup
4		Condition 1	Condition 2	Get this	Get this	Condition 3	Condition 3							Lookup Formula	Lookup Formula	Cust Code
5		↓	↓	↓	↓	↓	↓							↓	↓	↓
6	Name	Customer Code	Region	Rate	Disc Code	Eff From	Eff upto			Customer Code	Region	Transaction Date	Amt	Effective Rate	Disc Code	Name
7	John	JC005	North	12.00%		1-Jan-2015	18-Aug-2018	<--1		JC005	North	15-Jul-2019	17650		M2	John	*from 2
8	John	JC005	West	10.25%		2-Nov-2014	15-Sep-2019			JC005	North	12-Feb-2017	18200	12.00%		John	*from 1
9	Amy	AL162	North	5.60%	ACT	24-May-2018	14-Jan-2020			JC005	North	7-Feb-2020	6550	0.00%	Q1	John	*from 3
10	John	JC005	North		M2	19-Aug-2018	4-Jan-2020	<--2		JC005	North	28-Oct-2020	14350	15.00%	M1	John	*from 4
11	John	JC005	West	14.10%	Q5	16-Sep-2019				JC005	North	28-Dec-2020	10700	15.00%	M1	John	*from 4
12	John	JC005	North	0.00%	Q1	5-Jan-2020	7-Mar-2020	<--3		JC005	North	1-Nov-2014	5650			John	out of range
13	Brian	BK038	South	18.00%	ACT	1-Jan-2016											↑ here in the
14	Amy	AL162	North	7.00%	M3	15-Jan-2020				JC005	West	12-Oct-2020	13300	14.10%	Q5	John	last entry
15	John	JC005	North	15.00%	M1	8-Mar-2020		<--4		JC005	West	6-Sep-2018	17400	10.25%		John	both the
16	Amy	AL162	East	11.28%		1-Jun-2018											Rate &
17					↑		↑			BK038	South	5-May-2016	18800	18.00%	ACT	Brian	Disc code
18					Note:		Note: for the			BK038	South	30-Oct-2020	14650	18.00%	ACT	Brian	formula
19					return		current(open)										should
20					empty		period the			AL162	East	1-Sep-2020	8650	11.28%		Amy	return
21					where		Eff upto' date			AL162	North	1-Jan-2020	16650	5.60%	ACT	Amy	an error Or
22					empty		is left blank.			AL162	East	24-Dec-2021	8300	11.28%		Amy	a text msg
23										AL162	North	18-Jan-2020	5650	7.00%	M3	Amy
Sheet4

Cell Formulas
Range	Formula
N7:N23	N7	=IFERROR(--(INDEX(D:D,AGGREGATE(15,6,ROW(D$7:D$16)/($B$7:$B$16=$J7)/($C$7:$C$16=$K7)/($F$7:$F$16<=$L7)/(($G$7:$G$16>=$L7)+($G$7:$G$16="")),1))&""),"")
O7:O23	O7	=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW(E$7:E$16)/($B$7:$B$16=$J7)/($C$7:$C$16=$K7)/($F$7:$F$16<=$L7)/(($G$7:$G$16>=$L7)+($G$7:$G$16="")),1))&"","")

 

[Eric W]

Good job! Glad you got it sorted.