Answered by:
Vlookup formulas
Question

Based off the example data below, require formulas for the following criteria's
Lookup the Job Number and return the number of days between when the a job was created and the latest Diary dateLookup the Job Number and return the date of the latest Diary date record.Lookup the Job Number and return the Note of the latest Diary noteJob Number Job Created Diary Timestamp Diary Note Note By Job+Note Index 12773SV 01/07/2019 03/07/2019 Job Created by Shanaaze 12773SV1 12773SV 01/07/2019 04/07/2019 sent to queue by Shanaaze 12773SV2 12773SV 01/07/2019 06/07/2019 Email by James 12839SV3 12773SV 01/07/2019 10/07/2019 Job Scheduled by James 12839SV4 12773SV 01/07/2019 25/07/2019 Note by James 12839SV5  Edited by mctech2017 Thursday, July 4, 2019 1:35 AM
Answers

Let's say the data are on Sheet1 in A2:F1000, with headers in A1:F1.
On Sheet2, you have the job number to look up in B1.
The date of the latest diary record is given by
=MAXIFS(Sheet1!$C$2:$C$1000,Sheet1!$A$2:$A$1000,B1)
if you have a recent version of Excel, or by the following array formula confirmed with Ctrl+Shift+Enter for all versions:
=MAX(IF(Sheet1!$A$2:$A$1000=B1,Sheet1!$C$2:$C$1000))
Format the cell with the formula as a date. Let's say the formula is in B2.
The number of days between job created and latest diary date is given by
=B2VLOOKUP(B1,Sheet1!$A$2:$B$1000,2,FALSE)
Format the cell with this formula as General.
The corresponding note is given by the following array formula, confirmed with Ctrl+Shift+Enter:
=INDEX(Sheet1!$D$2:$D$1000,MATCH(1,1/((Sheet1!$A$2:$A$1000=B1)*(Sheet1!$C$2:$C$1000=B2)),0))
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by mctech2017 Thursday, July 4, 2019 12:21 PM
All replies

Let's say the data are on Sheet1 in A2:F1000, with headers in A1:F1.
On Sheet2, you have the job number to look up in B1.
The date of the latest diary record is given by
=MAXIFS(Sheet1!$C$2:$C$1000,Sheet1!$A$2:$A$1000,B1)
if you have a recent version of Excel, or by the following array formula confirmed with Ctrl+Shift+Enter for all versions:
=MAX(IF(Sheet1!$A$2:$A$1000=B1,Sheet1!$C$2:$C$1000))
Format the cell with the formula as a date. Let's say the formula is in B2.
The number of days between job created and latest diary date is given by
=B2VLOOKUP(B1,Sheet1!$A$2:$B$1000,2,FALSE)
Format the cell with this formula as General.
The corresponding note is given by the following array formula, confirmed with Ctrl+Shift+Enter:
=INDEX(Sheet1!$D$2:$D$1000,MATCH(1,1/((Sheet1!$A$2:$A$1000=B1)*(Sheet1!$C$2:$C$1000=B2)),0))
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by mctech2017 Thursday, July 4, 2019 12:21 PM



One issue with this formula, if there's multiple diary entries with the same date stamp for the one Job # it doesn't necessarily return the latest note. How would we alter the formula so that it uses the affix to determine the latest note?
Example from the data provided.
'12839SV5'  the affix '5' the highest number for that job number therefore it's the latest comment.





That complicates it  it means we can't simply take the MAX of the Job+Note Index values.
Would it be possible to store only the index as a number (1, 2, 3, …) in the column (or in another column if you prefer)? Or alternatively, use 12773SV01, 12773SV02 etc.
Either would make it a lot easier.
Regards, Hans Vogelaar (http://www.eileenslounge.com)


OK. The following array formula confirmed with Ctrl+Shift+Enter will return the note corresponding to the highest note index for the job:
=INDEX(Sheet1!$D$2:$D$1000,MATCH(B1&""&TEXT(MAX(IF(Sheet1!$A$2:$A$1000=B1,RIGHT(Sheet1!$F$2:$F$1000,2))),"00"),Sheet1!$F$2:$F$1000,0))
And the formula for the highest note index is (also as an array formula):
=B1&""&TEXT(MAX(IF(Sheet1!$A$2:$A$1000=B1,RIGHT(Sheet1!$F$2:$F$1000,2))),"00")
Regards, Hans Vogelaar (http://www.eileenslounge.com)