Adding another MATCH condition to array formula

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
Dear Reader,

I'm using this formula:
{=IFERROR(INDEX('Input (All Tickets)'!$B$2:$B$307;SMALL(IF(ISNUMBER(MATCH('Input (All Tickets)'!$R$2:$R$307;$C$3;0));MATCH(ROW('Input (All Tickets)'!$R$2:$R$307);ROW('Input (All Tickets)'!$B$2:$B$307));"");ROWS($A$4:$A4)));"")}

It's showing me the correct results based on C3. However, I'm trying to add another criteria to fine tune the results. The criteria value is placed in E3, with the search range being 'Input (All Tickets)'!$D$2:$D$307'.
So I basicly need to add (MATCH('Input (All Tickets)'!$D$2:$D$307;$E$3;0) to this formula so it only shows the results if both the condition in C3 and E3 are met.

I'm having trouble figuring out how to do that. Any help is much appreciated.

Kind regards,
Steve
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,759
Try...

=IFERROR(INDEX('Input (All Tickets)'!$B$2:$B$307,SMALL(IF('Input (All Tickets)'!$R$2:$R$307=$C$3,IF('Input (All Tickets)'!$D$2:$D$307=$E$3,ROW('Input (All Tickets)'!$B$2:$B$307)-ROW('Input (All Tickets)'!$B$2)+1)),ROWS($A$4:$A4))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Solution

Forum statistics

Threads
1,144,522
Messages
5,724,830
Members
422,581
Latest member
samiak

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?

Disable AdBlock

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
Top