Vlookup based on multiple criteria/conditions

narendra

Board Regular
Joined
Apr 15, 2008
Messages
95
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
ABCDEFGHIJKLMNOPQ
1Source Data :Destination :
2Rate/Disc MasterTransaction List
3(lookups reqd. here)Needs a betterNeeds aLookup
4Condition 1Condition 2Get thisGet thisCondition 3Condition 3Lookup FormulaLookup FormulaCust Code
5
6NameCustomer CodeRegionRateDisc CodeEff FromEff uptoCustomer CodeRegionTransaction DateAmtEffective RateDisc CodeName
7JohnJC005North12%Q21-Jan-201518-Aug-2018<--1JC005North15-Jul-20191765016.75%M2John*from 2
8JohnJC005West10.25%2-Nov-201415-Sep-2019JC005North12-Feb-20171820012%Q2John*from 1
9AmyAL162North5.6%ACT24-May-201814-Jan-2020JC005North7-Feb-202065500%Q1John*from 3
10JohnJC005North16.75%M219-Aug-20184-Jan-2020<--2JC005North28-Oct-20201435015%M1John*from 4
11JohnJC005West14.1%Q516-Sep-2019JC005North28-Dec-20201070015%M1John*from 4
12JohnJC005North0%Q15-Jan-20207-Mar-2020<--3JC005North1-Nov-201456500%Johnout of range
13BrianBK038South18%ACT1-Jan-2016↑ here in the
14AmyAL162North7%M315-Jan-2020JC005West12-Oct-20201330014.1%Johnlast entry
15JohnJC005North15%M18-Mar-2020<--4JC005West6-Sep-20181740010.25%Johnboth the
16AmyAL162East11.28%1-Jun-2018Rate &
17BK038South5-May-20161880018%BrianDisc code
18Note:Note: for theBK038South30-Oct-20201465018%Brianformula
19returncurrent(open)should
20emptyperiod theAL162East1-Sep-2020865011.28%Amyreturn
21whereEff upto' dateAL162North1-Jan-2020166505.6%Amyan error Or
22emptyis left blank.AL162East24-Dec-2021830011.28%Amya text msg
23AL162North18-Jan-202056507%Amy
24
Sheet1
Cell Formulas
RangeFormula
N20:N23,N17:N18,N14:N15,N10:N12,N7:N8N7=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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
How about:

Book1
ABCDEFGHIJKLMNOPQ
1Source Data :Destination :
2Rate/Disc MasterTransaction List
3(lookups reqd. here)Needs a betterNeeds aLookup
4Condition 1Condition 2Get thisGet thisCondition 3Condition 3Lookup FormulaLookup FormulaCust Code
5
6NameCustomer CodeRegionRateDisc CodeEff FromEff uptoCustomer CodeRegionTransaction DateAmtEffective RateDisc CodeName
7JohnJC005North12.00%Q2January 1, 2015August 18, 2018<--1JC005NorthJuly 15, 20191765016.75%M2John*from 2
8JohnJC005West10.25%November 2, 2014September 15, 2019JC005NorthFebruary 12, 20171820012.00%Q2John*from 1
9AmyAL162North5.60%ACTMay 24, 2018January 14, 2020JC005NorthFebruary 7, 202065500.00%Q1John*from 3
10JohnJC005North16.75%M2August 19, 2018January 4, 2020<--2JC005NorthOctober 28, 20201435015.00%M1John*from 4
11JohnJC005West14.10%Q5September 16, 2019JC005NorthDecember 28, 20201070015.00%M1John*from 4
12JohnJC005North0.00%Q1January 5, 2020March 7, 2020<--3JC005NorthNovember 1, 20145650  Johnout of range
13BrianBK038South18.00%ACTJanuary 1, 2016↑ here in the
14AmyAL162North7.00%M3January 15, 2020JC005WestOctober 12, 20201330014.10%Q5Johnlast entry
15JohnJC005North15.00%M1March 8, 2020<--4JC005WestSeptember 6, 20181740010.25%0Johnboth the
16AmyAL162East11.28%June 1, 2018  Rate &
17BK038SouthMay 5, 20161880018.00%ACTBrianDisc code
18Note:Note: for theBK038SouthOctober 30, 20201465018.00%ACTBrianformula
19returncurrent(open)  should
20emptyperiod theAL162EastSeptember 1, 2020865011.28%0Amyreturn
21whereEff upto' dateAL162NorthJanuary 1, 2020166505.60%ACTAmyan error Or
22emptyis left blank.AL162EastDecember 24, 2021830011.28%0Amya text msg
23AL162NorthJanuary 18, 202056507.00%M3Amy
Sheet1
Cell Formulas
RangeFormula
N14:O23,N7:O12N7=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)),"")
 
Upvote 0
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.
 
Upvote 0
That would require a minor change to the O7 formula, but you need to leave the N7 formula alone.

Book1
NO
1
2
3Needs a betterNeeds a
4Lookup FormulaLookup Formula
5
6Effective RateDisc Code
716.75%M2
812.00%Q2
90.00%Q1
1015.00%M1
1115.00%M1
12  
13  
1414.10%Q5
1510.25% 
16  
1718.00%ACT
1818.00%ACT
19  
2011.28% 
215.60%ACT
2211.28% 
237.00%M3
Sheet1
Cell Formulas
RangeFormula
N7:N23N7=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:O23O7=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))&"","")
 
Upvote 0
Solution
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
ABCDEFGHIJKLMNOPQ
1Source Data :Destination :
2Rate/Disc MasterTransaction List
3(lookups reqd. here)Needs a betterLookup
4Condition 1Condition 2Get thisGet thisCondition 3Condition 3Lookup FormulaLookup FormulaCust Code
5
6NameCustomer CodeRegionRateDisc CodeEff FromEff uptoCustomer CodeRegionTransaction DateAmtEffective RateDisc CodeName
7JohnJC005North12.00%1-Jan-201518-Aug-2018<--1JC005North15-Jul-201917650 M2John*from 2
8JohnJC005West10.25%2-Nov-201415-Sep-2019JC005North12-Feb-20171820012.00% John*from 1
9AmyAL162North5.60%ACT24-May-201814-Jan-2020JC005North7-Feb-202065500.00%Q1John*from 3
10JohnJC005NorthM219-Aug-20184-Jan-2020<--2JC005North28-Oct-20201435015.00%M1John*from 4
11JohnJC005West14.10%Q516-Sep-2019JC005North28-Dec-20201070015.00%M1John*from 4
12JohnJC005North0.00%Q15-Jan-20207-Mar-2020<--3JC005North1-Nov-20145650  Johnout of range
13BrianBK038South18.00%ACT1-Jan-2016  ↑ here in the
14AmyAL162North7.00%M315-Jan-2020JC005West12-Oct-20201330014.10%Q5Johnlast entry
15JohnJC005North15.00%M18-Mar-2020<--4JC005West6-Sep-20181740010.25% Johnboth the
16AmyAL162East11.28%1-Jun-2018  Rate &
17BK038South5-May-20161880018.00%ACTBrianDisc code
18Note:Note: for theBK038South30-Oct-20201465018.00%ACTBrianformula
19returncurrent(open)  should
20emptyperiod theAL162East1-Sep-2020865011.28% Amyreturn
21whereEff upto' dateAL162North1-Jan-2020166505.60%ACTAmyan error Or
22emptyis left blank.AL162East24-Dec-2021830011.28% Amya text msg
23AL162North18-Jan-202056507.00%M3Amy
Sheet4
Cell Formulas
RangeFormula
N7:N23N7=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:O23O7=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))&"","")
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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