conditional vlookup

[tanksalevikrant]

I am looking to have a conditional look up. Sample file is attached here.

I have three sheets in the file. Sheet 1 is where i want to capture last purchase date of an item code, which will be taken from sheet 2008 first, if not available for that item, then it will go to sheet 2007. If it is not available in both of the sheets, then I want the cell to be displayed with "-" & not "#N/A".
In the last column I want to calculate the number of days from last purchase date to 31-12-08.

thanks in advance

[JONvdHeyden]

In Sheet1 cell E5 and copied down to end of range:

=MAX(IF('2008'!$A$2:$A$100=$A5,'2008'!$C$2:$C$100),IF('2007'!$A$2:$A$100=$A5,'2007'!$C$2:$C$100))

This is an array formula and should be committed with CONTROL+SHIFT+ENTER and not just ENTER. Format the cells as d-mm-yyyy;;-

In F5 and copied to end of range:

=F2-E5

Format this cell as general or as a number, not a date.

[zbor]

Try this in E5 and extend down...

=IF(ISNA(VLOOKUP(A5;'2008'!$A$2:$D$2;3;FALSE));IF(ISNA(VLOOKUP(A5;'2007'!$A$2:$D$2;3;FALSE));"-";VLOOKUP(A5;'2007'!$A$2:$D$2;3;FALSE));VLOOKUP(A5;'2008'!$A$2:$D$2;3;FALSE))

In F5:

=IF(E5="-";"-";E5-$F$2)

[DonkeyOte]

Another alternative approach:

E5: =LOOKUP(10^10,CHOOSE({1,2,3},0,VLOOKUP(A5,'2007'!$A:$C,3,0),VLOOKUP(A5,'2008'!$A:$C,3,0)))
copy down as required

Apply Custom Format to E5:Ex of: [=0]"-";dd-mmm-yy

F5: =IF($E5,$F$2-$E5,"n/a")
copy down as required

[tanksalevikrant]

Something not working there. i tried those formulas. am attaching the file again with more example & indicating the desired results. the last date of purchase should pop-up between the choice of purchase data for 2 years which is split in 2 different sheets.

[DonkeyOte]

Jon's formula (post#2) will return the expected results, note though it is an Array and must be entered with CTRL + SHIFT + ENTER (not just enter)

[tanksalevikrant]

thanks a ton ! it worked..