Get column index matching the date value in excel-CodePudding

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!!!

CodePudding user response：

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

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