none
Vlookup formulas RRS feed

  • 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 date
    Lookup 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 note

    Job Number Job Created Diary Timestamp Diary Note Note By Job+Note Index
    12773SV 01/07/2019 03/07/2019 Job Created by Shanaaze 12773SV-1
    12773SV 01/07/2019 04/07/2019 sent to queue by Shanaaze 12773SV-2
    12773SV 01/07/2019 06/07/2019 Email by James 12839SV-3
    12773SV 01/07/2019 10/07/2019 Job Scheduled by James 12839SV-4
    12773SV 01/07/2019 25/07/2019 Note by James 12839SV-5


    • Edited by mctech2017 Thursday, July 4, 2019 1:35 AM
    Thursday, July 4, 2019 1:34 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

    =B2-VLOOKUP(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
    Thursday, July 4, 2019 7:39 AM

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

    =B2-VLOOKUP(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
    Thursday, July 4, 2019 7:39 AM
  • Thank you worked well. One formula i forgot to request:

    How do i complete the following?

    Lookup the Job Number and return the highest 'Job+Note Index' number(Column F)


    Thursday, July 4, 2019 8:42 AM
  • That's similar to the last formula that I posted:

    =INDEX(Sheet1!$F$2:$F$1000,MATCH(1,1/((Sheet1!$A$2:$A$1000=B1)*(Sheet1!$C$2:$C$1000=B2)),0))

    confirmed with Ctrl+Shift+Enter


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 4, 2019 9:51 AM
  • 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.

    '12839SV-5'   - the affix '-5' the highest number for that job number therefore it's the latest comment.        

    Thursday, July 4, 2019 12:41 PM
  • Why does job 12773SV have note indexes starting with 12773SV and also with 12839SV?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 4, 2019 2:05 PM
  • Sorry that my mistake in the sample data, they should all start with '12773SV'
    Thursday, July 4, 2019 2:13 PM
  • Could there be more than 9 notes for a particular job (in other words, could the note index end in -10, -11, etc.?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 4, 2019 2:40 PM
  • Yes some go up to 30.
    Thursday, July 4, 2019 10:39 PM
  • 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 12773SV-01, 12773SV-02 etc.

    Either would make it a lot easier.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 5, 2019 7:54 AM
  • sure, i've changed it to this format '12773SV-01"
    Saturday, July 6, 2019 4:35 AM
  • 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)

    Saturday, July 6, 2019 8:17 AM