Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-26-2019, 11:21 AM
rbul1 rbul1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2011
Location: KS
Posts: 21
rbul1 is on a distinguished road
Default Vlookup fails to select latest date/amt

Col. D E F G

DATE Dec31 bal. On hand Last Qtr Value


=Vlookup(Max($D$4:$D20),$D$4:$G20,2,T) formula in D4

row 4 12/31/2018 $26.50 $24.00 $25.50
01/31/2019 $26.50 $23.50 $24.00
02/28/2019 $26.50 $24.25 $23.50

MS Excel 2007 Spreadsheet, Windows 7

Last edited by rbul1; 01-26-2019 at 11:31 AM. Reason: to correct alignment of data
Reply With Quote
  #2  
Old 01-26-2019, 04:01 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 656
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Would you mind putting that into a spreadsheet and attaching the spreadsheet to your post, thanks.
Reply With Quote
  #3  
Old 01-26-2019, 11:03 PM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 386
ArviLaanemets will become famous soon enough
Default

1. When you enter the formula into cell D4 with parameters as in your post, then both VLOOKUP() and MAX() parts have circular reference;
2. What is the value for parameter T in VLOOKUP()? And why not simply 0?
Reply With Quote
  #4  
Old 01-27-2019, 12:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,294
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 01-27-2019, 03:07 AM
alpha alpha is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 15
alpha is on a distinguished road
Default

Quote:
Originally Posted by rbul1 View Post
=Vlookup(Max($D$4:$D20),$D$4:$G20,2,T) formula in D4
Try this, but with the formula not in D4 but in a cell outside the data table:
Code:
=VLOOKUP(MAX($D$4:$D20);$D$4:$G20;2;0)
Reply With Quote
  #6  
Old 02-16-2019, 08:19 AM
rbul1 rbul1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2011
Location: KS
Posts: 21
rbul1 is on a distinguished road
Default

Thank You, you were correct!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
return the status description relating to the latest date in garymon9AX Excel 2 08-21-2018 10:02 AM
What is the Latest Version of MS Office in 2018? adbast Word 3 05-15-2018 12:12 PM
How can I return Vlookup only if Specific Criteria is met in 1 column of the Vlookup Array EcommDOC Excel 7 01-22-2018 11:00 AM
Lookup & Import Earliest/Latest Date for Activity? ekeithjohnson Excel 1 08-25-2014 09:24 AM
the latest message in my inbox gets deleted Jmkary Outlook 1 01-27-2011 12:36 PM


All times are GMT -7. The time now is 05:45 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft