# INDEX and MATCH with multiple criteria is not returning required results

#### Sean15

##### Well-known Member
Hi:

The formulas in range J26:L28 are not returning the required results. Could you help please?

INDEX & MATCH with multiple criteria.xlsx
EFGHIJKL
21derekexec1250
22markpayroll1425
23paulIT5300
24
25ITexecpayroll
26derek#REF!1250#REF!
27mark#REF!1425#REF!
28paul#REF!5300#REF!
29
Sheet1
Cell Formulas
RangeFormula
J26:L28J26=INDEX(\$G\$21:\$G\$23,MATCH(\$I26,\$E\$21:\$E\$23,0), MATCH(J\$25,\$F\$21:\$F\$23,0))

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### StephenCrump

##### MrExcel MVP
It's not clear what you're trying to do. What are your required results in J26:L28?

#### Habtest

##### Board Regular
Is this what you are looking for?
Book1
EFGHIJKL
21derekexec1,250
22markpayroll1,425
23paulIT5,300
24
25ITexecpayroll
26derek 1,250
27mark 1,425
28paul5,300
Sheet1
Cell Formulas
RangeFormula
J26:L28J26=IFERROR(INDEX(\$G\$21:\$G\$23,MATCH(1,(\$E\$21:\$E\$23=\$I26)*(\$F\$21:\$F\$23=J\$25),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Sean15

##### Well-known Member
Is this what you are looking for?

Yes, but is a non array formula available? I don't want to use CTRL+SHIFT+ENTER to enter formula.

#### Alex Blakenburg

##### Well-known Member

Yes, but is a non array formula available? I don't want to use CTRL+SHIFT+ENTER to enter formula.

Does this do what you had in mind ?

20210722 Lookup MultiCriteria.xlsx
EFGHIJKLM
21derekexec1250
22markpayroll1425
23paulIT5300
24
25ITexecpayroll
26derek 1250
27mark  1425
28paul5300
29
Sheet1
Cell Formulas
RangeFormula
J26:L28J26=IFERROR(LOOKUP(2,1/((\$E\$21:\$E\$23=\$I26)*(\$F\$21:\$F\$23=J\$25)),\$G\$21:\$G\$23),"")

#### plshelpexcel

##### New Member
Perhaps this could also work for you:

Book2
EFGHIJKL
21derekexec1250
22markpayroll1425
23paulIT5300
24
25ITexecpayroll
26derek-1,250-
27mark--1,425
28paul5,300--
Sheet1
Cell Formulas
RangeFormula
J26:L28J26=+SUMIFS(\$G\$21:\$G\$23,\$E\$21:\$E\$23,\$I26,\$F\$21:\$F\$23,J\$25)

#### Sean15

##### Well-known Member
 J26:L28 J26 =+SUMIFS(\$G\$21:\$G\$23,\$E\$21:\$E\$23,\$I26,\$F\$21:\$F\$23,J\$25)

Thank you very much. But why is there + before SUMIFS?

#### plshelpexcel

##### New Member
Thank you very much. But why is there + before SUMIFS?
You’re welcome! The + before SUMIFS is not needed.

Replies
7
Views
78
Replies
9
Views
154
Replies
3
Views
175
Replies
1
Views
104
Replies
1
Views
102

1,141,303
Messages
5,705,590
Members
421,400
Latest member
chakam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back