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?
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. |