use multiple criteria in a INDEX/MATCH Formula

[Shekhar12]

Please suggest the solution to use multiple criteria in a INDEX/MATCH Formula.

[Sam.D]

The following examples shows use of multiple criteria in INDEX/MATCH Formula


Assume you have following data:

d1: Part e1: Code f1: Price g1: Find Part h1: Find Code
d2: a e2: 21 f2: 20.00 g2: a h2: 22
d3: a e3: 22 f3: 24.00 g3: b h3: 21
d4: b e4: 21 f4: 28.00 g4: a h4: 22
d5: b e5: 22 f5: 32.00 g5: a h5: 21


Suppose that you want to find the price for part y with code 21. [The input cells are g2 and h2] To find the price for this part, type the below formula in cell i2:

=INDEX($f$2:$f$5,MATCH(g2,IF($e$2:$e$5=h2,$d$2:$d$5),0))

[Raine]

Following example will clear your problem of how to use multiple criteria in a INDEX/MATCH Formula

i). Open Excel.
ii). Type the below data into a new worksheet:

L1: Part M1: Code N1: Price O1: Find Part P1: Find Code
L2: b M2: 11 N2: 5.00 O2: a P2: 12
L3: b M3: 12 N3: 6.00 O3: a P3: 11
L4: a M4: 11 N4: 7.00 O4: b P4: 12
L5: a M5: 12 N5: 8.00 O5: b P5: 11


iii). To find the price for part a with code 12 and return the value to cell Q2, type the following formula in cell Q2:
=INDEX($N$2:$N$5,MATCH(O2,IF($M$2:$BM$5=P2,$L$2:$L$5),0))
iv). Press CTRL+SHIFT+ENTER for enter the above formula as an array formula.

The above formula returns the value 8.00.
v). Click on cell Q2, capture the fill handle, and then fill down to cell Q5 to find the price for each part & code combination.

[JonathanD]

Hi,

If data of your excel sheet is arranged in row, then please use following process.
a. open Excel window.
b. Type the below data in the a new worksheet:

p1: Part q1: x r1: x s1: y t1: y
p2: Code q2: 11 r2: 12 s2: 11 t2: 12
p3: Price q3: 7.00 r3: 8.00 s3: 9.00 t3: 10.00
p4: Find Part q4: y r4: y s4: x t4: x
p5: Find Code q5: 12 r5: 11 s5: 12 t5: 11


c. To get the price of part y which has code 12 and return the value to cell q6, use the following formula in cell q6:
=INDEX($q$3:$t$3,MATCH(q4,IF($q$2:$t$2=q5,$q$1:$t$1),0))
d. Press CTRL+SHIFT+ENTER for entering the formula as an array formula.

e. Select cell q6 and then fill right to cell t6 to get the price for each part along with code combination.