Strange vlookup issue

[s80NL]

Hi all,

I have an issue with vlookup... I register data on a per week basis with a key in front of it (eg. 2007-04 stands for week 4 in 2007). Every week I need to calculate a figure using the previous week result. All is working well except for getting the value from the previous year when it's week 1 - it should then get the first value where the key starts with: 2006-

Example is in the attachment.

Cheers.

Steven

[Bryan Hessey]

Hi all,

I have an issue with vlookup... I register data on a per week basis with a key in front of it (eg. 2007-04 stands for week 4 in 2007). Every week I need to calculate a figure using the previous week result. All is working well except for getting the value from the previous year when it's week 1 - it should then get the first value where the key starts with: 2006-

Example is in the attachment.

Cheers.

Steven

Hi,

Your VLookup is incorrect, you are attempting a 'sorted acsending' lookup on a descending table with data that preceedes the first table item, this must fail.

The full formula is

=IF(A4="01",VLOOKUP(($B$1-1)&"-"&MAX(IF(LEFT(E9:E17,4)=TEXT(B1-1,"0000"),VALUE(RIGHT(E9:E17,2)))),$E:$F,2,FALSE),VLOOKUP($B$1&"-"&$A4,$E:$F,2,FALSE))

however, I recommend that you break this into two parts, in B2 put

=MAX(IF(LEFT(E9:E17,4)=TEXT(B1-1,"0000"),VALUE(RIGHT(E9:E17,2))))

and use

=IF(A4="01",VLOOKUP(($B$1-1)&"-"&B2,$E:$F,2,FALSE),VLOOKUP($B$1&"-"&$A4,$E:$F,2,FALSE))

note, the 'B2' portion of the formula requires CSE (CTRL/Sift/Enter), otherwise #Value will be the result.

hth
---

[rylo]

Steven

Formula for B10

Please Login or Register  to view this content.

Not sure why you want to get the previous entry in B4 as it seems inconsistent with the entries below, but you could use the same approach as B10.

rylo

[s80NL]

Hi guys,

Thanks a million for the timely responses

I have never realized that vlookup is actually made for ascending... I was looking for the cause of the malfunctioning in the last part of the function, the false / true statement.

@Rylo: the example is not complete accurate for the situation it will be used in... each week we are taking a "snapshot" of our stock levels in the data center and we do some calculations on that so we get the actual usage per week (corrected for the deployment of batches of new stock in a particular week).
We used to do this by hand every week - but now I'm rebuilding the whole sheet to get things more automated with some macro's and so on. In this scenario we have to be prepared for the over the year moment in which we still have to use the data of the previous week.

By the way, I decided to go for the INDEX / MATCH construction Rylo suggested, it works fine and is nice and short