what's wrong with this formula?
- Thread starter jrr
- Start date
AliGW
Administrator
- Joined
- Nov 8, 2015
- Messages
- 1,846
- Reaction score
- 8
- Points
- 38
- Location
- Ipswich, Suffolk, England
- Excel Version(s)
- MS 365 (Beta Insider Channel)
Define "doesn't work". What goes wrong? Do you get an error message? What result are you expecting?
Why use OFFSET in the first place?
Why use OFFSET in the first place?
Formula as stated isn't accepted.
I'm using the =OFFSET function as I want the Max function in column 1 (the most recent of a list of dates) to return the value in the 5th column of that row, an amount of money.
Suggest a correction to the formula, or a workaround, please?
Thanks for your interest!
caabyyc
Member
- Joined
- Dec 16, 2014
- Messages
- 52
- Reaction score
- 1
- Points
- 8
- Location
- YYC/YVR/YXX
- Excel Version(s)
- O365
to get the cell reference(with max value) in column A of worksheet "LesLoan" NOTE: the contect in criteria area must be in numercial format.
=OFFSET(INDIRECT("LesLoan!A"&MATCH(MAX(LesLoan!A:A),LesLoan!A:A,)),0,5)
to get the cell reference(with last entry) in column A of worksheet "LesLoan". NOTE: the content might or might not be numerical data
=OFFSET(INDIRECT("LesLoan!A"&LOOKUP(1,0/(LesLoan!A:A<>""),ROW(LesLoan!A:A))),0,5)
=OFFSET(INDIRECT("LesLoan!A"&MATCH(MAX(LesLoan!A:A),LesLoan!A:A,)),0,5)
to get the cell reference(with last entry) in column A of worksheet "LesLoan". NOTE: the content might or might not be numerical data
=OFFSET(INDIRECT("LesLoan!A"&LOOKUP(1,0/(LesLoan!A:A<>""),ROW(LesLoan!A:A))),0,5)
Attachments
Pecoflyer
Admin Alumnus
- Joined
- Oct 13, 2011
- Messages
- 1,803
- Reaction score
- 1
- Points
- 38
- Location
- Brussels Belgium
- Excel Version(s)
- 2010 on Xubuntu - O365
Wow ! volatile functions in the same formula ! Strange idea
What's wrong with a simple INDEX/MATCH combination?
What's wrong with a simple INDEX/MATCH combination?