VLOOKUP which gives highest value

[cpisthedbb]

Hi,

The spreadsheet I am using has 3 orders which make one bulk order. The 3 orders have 3 different finish dates. Is there a way of putting on a vlookup formula which then chooses the highest date (this is to create a pivot table).

For example:

Order Finish date Bulk order
1 01/01/18 500
2 05/01/18 500
3 06/01/18 500

So in this example I need to look up the last finish date for Bulk order 500 which is 06/01/18 and ignore the 01/01 & 05/01.

Hope this makes sense.

 

[Peter_SSs]

If you have the MAXIFS function (Office 365) you can use F2
If you have Excel 2010 or later you can use G2
H2 for all versions but this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Order Finish date	Bulk order			Bulk order	Last date
2	21/01/2018	400			500	6/01/2018	6/01/2018	6/01/2018
3	2/01/2018	400			400	21/01/2018	21/01/2018	21/01/2018
4	1/01/2018	500
5	5/01/2018	500
6	6/01/2018	500
Highest date

 

[cpisthedbb]

If you have the MAXIFS function (Office 365) you can use F2
If you have Excel 2010 or later you can use G2
H2 for all versions but this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Highest date

	A	B	C	D	E	F	G	H

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:121px;"><col style="width:78px;"><col style="width:25px;"><col style="width:21px;"><col style="width:74px;"><col style="width:103px;"><col style="width:97px;"><col style="width:97px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Order Finish date[/TD]
[TD="align: right"]Bulk order[/TD]

[TD="align: right"]Bulk order[/TD]
[TD="align: right"]Last date[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]400[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]6/01/2018[/TD]
[TD="align: right"]6/01/2018[/TD]
[TD="align: right"]6/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2/01/2018[/TD]
[TD="align: right"]400[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]21/01/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1/01/2018[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]5/01/2018[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]6/01/2018[/TD]
[TD="align: right"]500[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =MAXIFS(A$2:A$6,B$2:B$6,E2) G2 =AGGREGATE(14,6,A$2:A$6/(B$2:B$6=E2),1) H2 {=MAX(IF(B$2:B$6=E2,A$2:A$6))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for the quick reply. I'm using 2010 Excel- for some reason G2 formula isn't giving me the dates and H2 I'm getting the #NUM ! error. I must be doing something wrong but I'm not sure what...

 

[Peter_SSs]

G2 formula isn't giving me the dates ..

What is G2 giving you?

.. and H2 I'm getting the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error.

Do you have any existing errors (eg #NUM !) in columns A:B?

If you try the G2 & H2 formulas in a new sheet with the sample data I used manually entered, do they work for you?

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[cpisthedbb]

What is G2 giving you?


Do you have any existing errors (eg #NUM !) in columns A:B?

If you try the G2 & H2 formulas in a new sheet with the sample data I used manually entered, do they work for you?
.

What is G2 giving you?


Do you have any existing errors (eg #NUM !) in columns A:B?

If you try the G2 & H2 formulas in a new sheet with the sample data I used manually entered, do they work for you?

Thanks for the reply. G2 gives the date 00/01/00. The columns a:b do have some n/a values in as it works off a VLOOKUP. Is this likely the issue?

The formula did work when I copied and pasted your table so is my spreadsheet.

 

[Peter_SSs]

G2 gives the date 00/01/00.

That would seem to indicate that the only rows for that Bulk order number have zero or blank cells in column A. Example below. Is that the case?

Excel Workbook
	A	B	C	D	E	G
1	Order Finish date	Bulk order			Bulk order	Last date
2	21/01/2018	400			500	0/01/1900
3	N/A	400			400	21/01/2018
4		500
5	N/A	500
6		500
Highest date

The columns a:b do have some n/a values in as it works off a VLOOKUP. Is this likely the issue?

Yes it is, I suggest that you change those column A VLOOKUP formulas as follows & see how that goes.

=IFERROR(your_existing_vlookup,"N/A")

 

[cpisthedbb]

Hi Peter,

Late reply but I've only just got around to re-looking at this. I managed to get this to work so thanks for your help, once again you've come up trumps!

 

[Peter_SSs]

You’re welcome. Glad you were able to resolve the final issues.