Date lookup to get week back

My vlookup fonction fell in a black hole after the november 23rd 2009.

What would you reckon?

These are not "real" date entries..they are text strings.

Select column of "dates" and go to Data|Text to Columns.

Then skip to the 3rd window and select Date and select MDY from the drop down in the column data format area.

Click Finish.

Repeat for date column in other sheet.

An alternative is to replace your formula with:

=INDEX(Sheet2!$C$1:$C$22,MATCH(B2+0,Sheet2!$B$1:$B$22+0))

and confirm it with CTRL+SHIFT+ENTER not just ENTER so you will see { } brackets appear around the formula

Then copy it down.

Maybe even

=INDEX(Sheet2!$C$1:$C$22,MATCH(TRUE,(INDEX((Sheet2!$B$1:$B$22+0>=B2+0),0)),0)-1)

without special confirmation.. just ENTER

Also, I tried replacing the comas with semicomas to seperate the expressions.
And i get a #n/a for the second formula, and a # value for the first one.

Still giving problems? Using formula 2...see attached...

Formula 1, you need to Confirme with CTRL+SHIFT+ENTER keys at once.

Are you sure you are not accidentally deleting or changing part of the formula?

For me it was the opposite when I opened your workbook in Post #7 above...

It was #N/A, I press F2 and enter, and formula is okay...

Not sure maybe something in the version translation...

Can you try entering yourself:

=INDEX(Sheet2!$C$1:$C$22;MATCH(B2+0;Sheet2!$B$1:$B$22+0))

and then hold the CTRL and SHIFT keys down and press ENTER...

Then copy down.

Does that work at all?

It must have to do with the data formatting then...

if you go to the formula in D2 and then go to Tools|Formula Auditing and click Evaluate Formula... then click Evaluate until you get to the last evaluation before the final answer... what do you see in the Evaluation box?

Give this a try:

=INDEX(Sheet2!$C$1:$C$22;MATCH(TRUE;(INDEX((DATE(MID(Sheet2!$B$1:$B$22;7;4);LEFT(Sheet2!$B$1:$B$22;2);MID(Sheet2!$B$1:$B$22;4;2))+0>=DATE(MID(B2;7;4);LEFT(B2;2);MID(B2;4;2))+0);0));0)-1)

No, I don't easily like to give up...

Glad that worked.

I think it was just are dates being expressed differently...

Can you also please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

We don't take payments or donations really.

We are volunteers doing this for fun and brain exercise.

The fact that we have helped you get the results that make you happy is good enough (for me, at least)... and you have shown your appreciation, which makes it worthwhile.