Index Match with more than one criteria

[prov1x212]

I am trying to get excel to return a date from a list of orders from certain companies. This list has the same companies just different dates and i need excel to return the latest date.

=INDEX(Orders!B313:B419,MATCH(1,(Orders!A313:A419="*"&F3&"*")*(Orders!B313:B419>N3),1))

F3 is the name of the company and i'm using the "*" because on the orders page it has more information in the line. This may be an incorrect way to do it but I've used it in the past.
N3 is the 2nd to last order and i'm looking to find the last order. I did press CTL Shift Enter when i tried this.

Any help would be great. Thanks

 

[AhoyNC]

Try this array formula:

[TABLE="width: 746"]
<colgroup><col width="746"></colgroup>[TR]
   [TD="width: 746"]INDEX(Orders!B313:B419,MATCH(1,(ISNUMBER(SEARCH(F3,Orders!A313:A419)))*(Orders!B313:B419>N3),0))[/TD]
 [/TR]
[/TABLE]

If you just need the latest date based on a company take a look at the MAXIFS function if you have Excel 2016 (see cell E3 below) or an array formula with MAX function see cell E5) in the example below.

Excel Workbook
	A	B	C	D	E
1	Company	Date
2	Comp1 T2	4/5/2018		Company	Last Order
3	Comp2	43258		Comp1	6/8/2018
4	Comp1 T2	2/1/2018
5	Comp1 T2	6/8/2018			6/8/2018
6	Comp2	9/12/2018
7	Comp6	3/5/2018
8	Comp1 T2	5/9/2018
9	Comp2	7/12/2018
10	Comp9	8/30/2018
Sheet

 

[prov1x212]

Awesome thanks! I was able to find the second to last by just adding a second criteria less than the first.

 

[prov1x212]

How about finding the related shipping amount. Again i tried to use Index with match and an array but that doesn't ever seem to go well for me.

 

[AhoyNC]

Reference the Shipping Amount range in the INDEX instead of the date.

INDEX(Orders![COLOR=#ff0000]Ship Amount Range Here[/COLOR],MATCH(1,(ISNUMBER(SEARCH(F3,Orders!A313:A419)))*(Orders!B313:B419>N3),0))

Excel Workbook
	A	B	C	D	E	F	G	H	M	N
1
2						Company	Ship$			Date
3						Comp1	$80.00			5/9/2018
4
5
6
311
312	Company	Date	Ship$
313	Comp1 T2	4/5/2018	$79.00
314	Comp2	6/7/2018	$42.00
315	Comp1 T2	2/1/2018	$77.00
316	Comp1 T2	6/8/2018	$80.00
317	Comp2	9/12/2018	$62.00
318	Comp6	3/5/2018	$65.00
319	Comp1 T2	5/9/2018	$42.00
320	Comp2	7/12/2018	$47.00
321	Comp9	8/30/2018	$76.00
Sheet