## 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**

MATCH(TRUE, B2:D2<>"Need to Overlook", 0)

becomes

MATCH(TRUE, {"ACC","Need to Overlook","Need to Overlook"}<>"Need to Overlook", 0)

becomes

MATCH(TRUE, {TRUE,FALSE,FALSE}, 0)

and returns 1.

**Step 2 - Return cell reference**

INDEX((B2, C2, D2), , , MATCH(TRUE, B2:D2<>"Need to Overlook", 0))

becomes

INDEX((B2, C2, D2), , , 1)

and returns cell reference B2

*Step 3 - Find matching row*

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 4 - Calculate relative position of row*

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 5 - Return margin value**

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

### Get excel *.xlsx file

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

**Contact Oscar**

You can contact me through this contact form

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?