# INDEX MATCH formula with criteria

#### cjcass

##### Well-known Member
Hi,
I'm looking for a formula please (not vba) to enter into cell E15 that will reference the criteria entered into cells E12 & E13 and also the operators in cells B4 & C4 and give the result from the table. Note, the operators in cells B4 & C4 may change hence the need for them to be referenced.
Any help much appreciated.

#### Attachments

• indexmatch.JPG
37.7 KB · Views: 11

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Based on the screen capture
Excel Formula:
``=INDEX(D5:F7,MATCH(E12,B5:B7,-1),MATCH(E12,D4:F4,0))``

in cell E15 place the following formula:
Excel Formula:
``=INDEX(D4:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))``
investigate index and match

Based on the screen capture
Excel Formula:
``=INDEX(D5:F7,MATCH(E12,B5:B7,-1),MATCH(E12,D4:F4,0))``
Hi,
Thanks for your time but this formula isn't working, it also doesn't reference column C

in cell E15 place the following formula:
Excel Formula:
``=INDEX(D4:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))``
investigate index and match
Hi, thanks but this returns 270 instead of 350 and doesn't reference Col B

Hi,
Thanks for your time but this formula isn't working, it also doesn't reference column C
Typo in the formula, it happens sometimes when you post screen captures instead of usable (XL2BB) examples. Also it doesn't need to reference column C, matching to B is adequate based on the example provided.
Book1
BCDEF
4<>=USAGermanyFrance
510720027067
67530035087
754400430107
840500510127
9
10
11
126
13Germany
14
15350
Sheet1
Cell Formulas
RangeFormula
E15E15=INDEX(D5:F8,MATCH(E12,B5:B8,-1),MATCH(E13,D4:F4,0))

Hi, thanks but this returns 270 instead of 350 and doesn't reference Col B
ok, spotted a slight issue and made the change to... =INDEX(D5:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))
This works on one operator column only and is workable if the Col B & Col C entries and cleanly sequential, but if they are not is there a formula that will consider both Cols..?

ok, spotted a slight issue and made the change to... =INDEX(D5:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))
This works on one operator column only and is workable if the Col B & Col C entries and cleanly sequential, but if they are not is there a formula that will consider both Cols..?
Please ignore my last comment, that request is overkill.
Thank you all for your input and the solution, much appreciated.

try :
Book1
ABCDEF
1
2
3
4USAGermanyFrance
510720027067
67530035087
7544004430107
840500510127
9
10
11CRITERIA
12NUMBER6
13COUNTRYGermany
14
15Formula Resuts:350
Sheet1
Cell Formulas
RangeFormula
E15E15=INDEX(\$D\$5:\$F\$8,AGGREGATE(15,6,(ROW(\$D\$5:\$F\$8)-ROW(\$D\$5)+1/((\$B\$5:\$B\$8<\$E\$12)+(\$C\$5:\$C\$8<=\$E\$12))),ROWS(\$B\$5:B5)),MATCH(E13,D4:F4,0))

Replies
3
Views
176
Replies
3
Views
299
Replies
3
Views
181
Replies
18
Views
871
Replies
2
Views
508

1,206,711
Messages
6,074,475
Members
446,071
Latest member
gaborfreeman

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