Index + match multiple criteria

[MikoSDS]

Hello Everyone,

I am looking for help regarding this formula, because currently I don’t know how to find the newest date in my sheet, below the formula

={INDEX('all opps'!$E:$E,MATCH(1,('no wip opps'!$H3='all opps'!$F:$F)*('no wip opps'!$C3<'all opps'!$G:$G),0))}

So in cell C3 I have a date and in column G there’s a lot of dates and I would like to find the newest date and it must be greater than date in cell C3.

Thanks in advance!

 

[kweaver]

Are the dates in G sorted?

=INDEX($G:$G,1+MATCH(C3,$G:$G,1))

 

[MikoSDS]

No, they are not

 

[AhoyNC]

Maybe:

Excel Workbook
	B	C	D	E	F	G
1						Dates
2						5/5/2019
3		5/6/2019				4/8/2019
4	Newest	7/1/2019				6/2/2019
5						7/1/2019
6						5/14/2019
Sheet

 

[DanteAmor]

No, they are not

Try this array formula

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >E</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">Date</td><td style="background-color:#ffff00; ">Values</td><td style="background-color:#ffff00; ">Dates</td><td style="background-color:#ffff00; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">15-abr</td><td > </td><td > </td><td style="background-color:#92d050; ">f15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >f4</td><td style="text-align:right; ">15-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >f5</td><td style="text-align:right; ">16-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >f6</td><td style="text-align:right; ">17-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >f7</td><td style="text-align:right; ">18-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >f8</td><td style="text-align:right; ">19-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >f9</td><td style="text-align:right; ">04-abr</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >f10</td><td style="text-align:right; ">05-abr</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >f11</td><td style="text-align:right; ">06-abr</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >f12</td><td style="text-align:right; ">07-abr</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >f13</td><td style="text-align:right; ">08-abr</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >f14</td><td style="text-align:right; ">27-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="background-color:#92d050; ">f15</td><td style="background-color:#92d050; text-align:right; ">10-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >f16</td><td style="text-align:right; ">29-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >f17</td><td style="text-align:right; ">30-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >f18</td><td style="text-align:right; ">31-may</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td >f19</td><td style="text-align:right; ">01-jun</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H3</td><td >{=INDEX(E1:E19,SUMPRODUCT((IF(G4:G19>C3,G4:G19-C3)=MIN(IF(G4:G19>C3,G4:G19-C3)))*(ROW(G4:G19))))}</td></tr></table></td></tr></table>

 

[MikoSDS]

Thank you guys for reply! But I think I did not explained it well. Let me try explain this again, so I have to get the "ID" by two criteria - one is "Name" and next is "Date" and it must be higher than date which is in C3 for example, I know the formula works good, when I'll sort the dates from newest to oldest, but I would like to do this without sorting

Check my screen and link to the file, hope it will be better to understand what I mean, thank you!!

https://ufile.io/9vergj8m

 

[AhoyNC]

Try this array formula.
Enter with CTRL-SHIFT-ENTER. Drag down as needed.

Excel Workbook
	C	D	E	F	G	H	I
1
2	Date	Name	Wanted ID		Date	Name	ID
3	5/15/2019	Text1	1111746		5/15/2019	Text1	556854
4	12/18/2016	Text2	23457865		5/17/2019	Text1	223134
5					8/20/2019	Text1	110586
6					8/23/2019	Text1	444306
7					12/13/2019	Text1	778026
8					12/13/2020	Text1	1111746
9					12/14/2016	Text1	1445466
10					12/15/2016	Text2	5542
11					12/17/2017	Text2	3432
12					12/18/2019	Text2	8768
13					12/20/2023	Text2	23457865
Sheet

 

[DanteAmor]

Thank you guys for reply! But I think I did not explained it well. Let me try explain this again, so I have to get the "ID" by two criteria - one is "Name" and next is "Date" and it must be higher than date which is in C3 for example, I know the formula works good, when I'll sort the dates from newest to oldest, but I would like to do this without sorting

Check my screen and link to the file, hope it will be better to understand what I mean, thank you!!

Try

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:19.01px;" /><col style="width:19.01px;" /><col style="width:89.35px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:22.81px;" /><col style="width:96.95px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Wanted ID</td><td style="background-color:#ffff00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="background-color:#92d050; text-align:right; ">15/05/2019</td><td style="background-color:#92d050; ">Text1</td><td style="background-color:#92d050; text-align:right; ">1111746</td><td > </td><td style="text-align:right; ">15/05/2019</td><td >Text1</td><td style="text-align:right; ">556854</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="background-color:#ffc000; text-align:right; ">18/12/2012</td><td style="background-color:#ffc000; ">Text2</td><td style="background-color:#ffc000; text-align:right; ">23457865</td><td > </td><td style="text-align:right; ">17/05/2019</td><td >Text1</td><td style="text-align:right; ">223134</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">23/08/2019</td><td >Text3</td><td style="text-align:right; ">0</td><td > </td><td style="text-align:right; ">20/08/2019</td><td >Text1</td><td style="text-align:right; ">110586</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">15/05/2025</td><td >Text2</td><td style="text-align:right; ">0</td><td > </td><td style="text-align:right; ">23/08/2019</td><td >Text1</td><td style="text-align:right; ">444306</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">13/12/2019</td><td >Text1</td><td style="text-align:right; ">778026</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#92d050; text-align:right; ">13/12/2020</td><td style="background-color:#92d050; ">Text1</td><td style="background-color:#92d050; text-align:right; ">1111746</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">14/12/2016</td><td >Text1</td><td style="text-align:right; ">1445466</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">15/12/2016</td><td >Text2</td><td style="text-align:right; ">5542</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">16/12/2016</td><td >Text2</td><td style="text-align:right; ">3432</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">17/12/2017</td><td >Text2</td><td style="text-align:right; ">76864</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">18/12/2016</td><td >Text2</td><td style="text-align:right; ">5343</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">18/12/2019</td><td >Text2</td><td style="text-align:right; ">8768</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#ffc000; text-align:right; ">20/12/2023</td><td style="background-color:#ffc000; ">Text2</td><td style="background-color:#ffc000; text-align:right; ">23457865</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E3</td><td >{=INDEX($I$1:$I$15,SUMPRODUCT(MAX(($G$3:$G$15>C3)*($H$3:$H$15=D3)*(ROW($G$3:$G$15)))))}</td></tr></table></td></tr></table>

 

[MikoSDS]

Thank you guys!! It's working perfectly now!

 

[DanteAmor]

Thank you guys!! It's working perfectly now!

I'm glad to help you. Thanks for the feedback.