Lookup using multiple conditions
Debraj Roy asks:Hi Oscar,
I failed to search for "Ask" Section.. so submitting my query here.. Apologize for hijacking someones post,, and also for cross-post..
https://chandoo.org/forums/topic/lookup-using-multiple-condition
Can You please help me to create a drag-able FORMULA to get Margin.. via multiple Condition..
* Each Vendor has only one type of MARGIN decider (MRP or BRICK or TAX Code)
* According to Margin decider, I need to get MARGIN for the VENDOR..
i.e Vendor 1 (Chandoo) 's Margin Decider is BRICK..
If brick is ACC then Margin is 548, if Brick is JEW then Margin is 786.. (Check Table2 for lookupArea and Margin Decider..
LookUpArea..
* suggestion for changing Design of LookUpArea's is appreciable.
* use of HELPER column is also acceptable..
https://dl.dropbox.com/u/78831150/Excel/MultipleLookup.xlsx
You can get the VBA version for your reference..
https://dl.dropbox.com/u/78831150/Excel/MultipleLookup.xlsm
Regards,
Deb
Answer:
Array formula in cell E2:
How to enter an array formula
- Select cell E2
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
How to copy array formula
- Select cell E2
- Copy cell E2 (Ctrl + c)
- Select cell range E3:E23
- Paste (Ctrl + v)
Explaining array formula in cell E2
Step 1 - Determine which cell range to use
The less than an larger than signs combined checks if a value is not equal to another value, the result si a boolean value TRUE or FALSE.
B2:D2<>"Need to Overlook"
becomes
{"ACC","Need to Overlook","Need to Overlook"}<>"Need to Overlook"
and returns
{TRUE, FALSE, FALSE}
Step 2 - Determine which cell range to use
The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.
Function syntax: MATCH(lookup_value, lookup_array, [match_type])
MATCH(TRUE, B2:D2<>"Need to Overlook", 0)
becomes
MATCH(TRUE,{TRUE, FALSE, FALSE} , 0)
and returns 1.
Step 3 - Return cell reference
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX((B2, C2, D2), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0))
becomes
INDEX((B2, C2, D2), , , 1)
and returns cell reference B2
Step 4 - Find a matching row
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.
Function syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
COUNTIFS(INDEX((B2, C2, D2), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), INDEX(($K$3:$K$11, $L$3:$L$11, $M$3:$M$11), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), A2, $J$3:$J$11)
becomes
COUNTIFS(B2, $K$3:$K$11, A2, $J$3:$J$11)
and returns
{1;0;0;0;0;0;0;0;0}.
Step 5 - Calculate the relative position of row
The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.
Function syntax: MATCH(lookup_value, lookup_array, [match_type])
MATCH(1, COUNTIFS(INDEX((B2, C2, D2), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), INDEX(($K$3:$K$11, $L$3:$L$11, $M$3:$M$11), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), A2, $J$3:$J$11), 0)
becomes
MATCH(1, {1;0;0;0;0;0;0;0;0}, 0)
and returns 1.
Step 6 - Return margin value
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX($N$3:$N$11, MATCH(1, COUNTIFS(INDEX((B2, C2, D2), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), INDEX(($K$3:$K$11, $L$3:$L$11, $M$3:$M$11), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0)), A2, $J$3:$J$11), 0))
becomes
INDEX($N$3:$N$11, 1)
becomes
INDEX({725; 588; 560; 616; 606; 731; 724; 646; 867}, 1)
and returns 725 in cell E2.
Functions in this article
More than 1300 Excel formulas
Excel categories
5 Responses to “Lookup using multiple conditions”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Thanks a lot.. for giving your time...
Can you please check the below file..
https://dl.dropbox.com/u/78831150/Excel/MultipleLookup.xlsm
* Which one is showing "Need to Overlook" they are VERIABLE..
* For each vendor I need to check his margin decider.. then according to margin Decider.. I need to LOOKUP.. correct Margin from Table 2..
Check attach file's Module.. for complete detail..
Apologize.. for increasing the confusion..
and requesting you to change the IMAGE (Lookup-multiple-conditions1.png) in post.. with latest one..
Regards,
Deb
Debraj,
Array formula in cell E2:
=INDEX($N$3:$N$11, MATCH(2, COUNTIF(A2, $J$3:$J$11)+COUNTIF(B2, $K$3:$K$11)+COUNTIF(C2, $L$3:$L$11)+COUNTIF(D2, $M$3:$M$11), 0))
Check this file:
MultipleLookup_solution.xlsm
Oscar..
amazing.. what an use of BINARY ADDITION..
Its always an eye pleasure to read your articles..
Thanks a ton...
Regards,
Deb
Hi Oscar,
One more silly question..
I tried to achieve the above by below formula..
=DGET(LookUpArea,"MARGIN",$P$2:$Q$3)
after Press F9 for $P$2:$Q$3 it gives me {"Vendor","Brick";"Chandoo","JEW"}
But if I set Criteria Array dynamic.. {"Vendor",E2;A2,Choose(Match,...)} it fails.. Is there any option to set ARRAY by Formula..
Regards,
=DEC2HEX(3563)
Debraj Roy,
Can you provide the array formula?