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

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