Search code examples
excelexcel-formulaexcel-2003

Get column index matching the date value in excel

Below the screenshot/formula I am trying to match the date value in Column E against another date range column in Column B. If found, then return column D's matching value.

FORMULA: ==LOOKUP(E3,$A$3:$B$46,$C$3:$C$46)

Current Output:

FROM    TO      AGE(Years)  INPUT   OUTPUT
7/4/65  10/4/65 0.25        7/4/68  0.25

Expected Output:

FROM    TO      AGE(Years)  INPUT   OUTPUT
7/4/65  10/4/65 0.25        1/4/68  **2.75**

NOTE: I am using EXCEL 2003!!!

enter image description here

Solution

  • You have to use index/match to get the value in column D corresponding to the match position in column E:

    =IFERROR(INDEX(D$3:D$46,MATCH(E3,$B$3:$B$46,0)),"No")

    However, according to this Iferror wasn't available until Excel 2007 so you'd have to use Iserror or Isna:

    =IF(ISNA(MATCH(D3,$B$3:$B$46,0)),"No",INDEX(C$3:C$46,MATCH(D3,$B$3:$B$46,0)))

    For the date ranges

    If you decide to use Vlookup:

    =VLOOKUP(D3,A$3:C$46,3,TRUE)

    Or the two versions of Lookup:

    =LOOKUP(D3,A$3:C$46)

=LOOKUP(D3,A$3:A$46,C$3:C$46)

    all give the same results.

    enter image description here

    Note that I am doing the lookup on column A and it doesn't give the same answer as an exact lookup on column B. This is because it's not clear whether 10/4/65 (for example) should produce .25 or .5 because both are possible as 10/4/65 is the end of one range and the start of another and I have chosen to go for the second one.

    Extra challenge

    Would it be fairly easy to do this without doing a lookup and just using Excel 2003 functions?

    Yes. If A1 contains the baseline date (maybe a date of birth) 4/4/65, it would look like this:

    =YEAR(D3)-YEAR(A$1)+INT((MONTH(D3)-MONTH(A$1)-(DAY(D3)<DAY(A$1)))/3)/4