Return Latest Value Matching Invoice Number

[Legacy 436357]

Hi,

Can someone please come up with a formula to put the latest Invoice Total in F10 matching INV number in A10 on the Status sheet.

The data is in the Lookup Invoice sheet.

Thank you

Excel Workbook
	J	K	L
2	Invoice Totals	Invoice Number	Time Stamp
3	$660.00	101	14:31:22
4	$140.00	103	14:31:43
5	$130.00	102	14:31:47
6	$660.00	101	14:34:56
7	$140.00	103	14:35:00
Lookup Invoice

Excel Workbook
	A	B	C	D	E	F
9	INV	CUST ID	CUSTOMER NAME	INVOICE DATE	DUE DATE	INVOICE AMOUNT
10	101	JAC6565	,Table3,0)),"")]Jack Benny	5/1/2019	,Table4,0)),"")]5/15/2019	$ 140.00
11	102	SUS8989	,Table3,0)),"")]Susan Maloney	5/6/2019	,Table4,0)),"")]5/20/2019	$ 660.00
12	103	JOH5555	,Table3,0)),"")]John Henry	6/8/2019	,Table4,0)),"")]6/22/2019	$ 130.00
Status

 

[DanteAmor]

Try:

Invoice sheet
<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:139.72px;" /><col style="width:139.72px;" /><col style="width:139.72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >J</td><td >K</td><td >L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Invoice Totals</td><td >Invoice Number</td><td >Time Stamp</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">$660.00</td><td style="text-align:right; ">101</td><td style="text-align:right; ">14:31:22</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">$140.00</td><td style="text-align:right; ">103</td><td style="text-align:right; ">14:31:43</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">$130.00</td><td style="text-align:right; ">102</td><td style="text-align:right; ">14:31:47</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">$660.00</td><td style="text-align:right; ">101</td><td style="text-align:right; ">14:34:56</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">$300.00</td><td style="text-align:right; ">103</td><td style="text-align:right; ">14:35:00</td></tr></table>



Status
<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:134.97px;" /><col style="width:134.97px;" /><col style="width:134.97px;" /><col style="width:134.97px;" /><col style="width:134.97px;" /><col style="width:134.97px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >INV</td><td >CUST ID</td><td >CUSTOMER NAME</td><td >INVOICE DATE</td><td >DUE DATE</td><td >INVOICE AMOUNT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">101</td><td >JAC6565</td><td >Jack Benny</td><td style="text-align:right; ">05/01/2019</td><td style="text-align:right; ">5/15/2019</td><td style="text-align:right; ">$660.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">102</td><td >SUS8989</td><td >Susan Maloney</td><td style="text-align:right; ">05/06/2019</td><td style="text-align:right; ">5/20/2019</td><td style="text-align:right; ">$130.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">103</td><td >JOH5555</td><td >John Henry</td><td style="text-align:right; ">06/08/2019</td><td style="text-align:right; ">6/22/2019</td><td style="text-align:right; ">$300.00</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>Formeln der Tabelle</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 >Zelle</td><td >Formel</td></tr><tr><td >F10</td><td >=SUMPRODUCT((Invoice!$K$3:$K$7=$A10)*(Invoice!$L$3:$L$7=MAX((Invoice!$K$3:$K$7=$A10)*(Invoice!$L$3:$L$7)))*(Invoice!$J$3:$J$7))</td></tr></table></td></tr></table>

 

[Legacy 436357]

Thank you very much that is perfect!

 

[DanteAmor]

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