Lookup Problems

[Brabed]

I have two worksheets. One (Worksheet 1) contains hourly data for the currencies market. The other (Worksheet 2) contains daily data. In column A of both sheets I have a numerical representation of the date. I'm trying to call up the high of the day of yesterday's daily data and compare with the current hourly data of today. I am trying a lookup function that looks like this:

=lookup(A1-1,'Worskheet2!A:A,Worksheet2!D:D)

This should return yesterday's high (Column D) which would be in the same row as yesterday's corresponding date number (A1-1). Now the problem I'm having is that instead of returning the correct value, it always retuns the value of the very last populated cell in column D. What am I doing wrong?

I have used similar LOOKUP formulas with no troubles. Let me know if I am not clear in my question.

 

[Brabed]

Problem solved. It didn't like that the numbers were in descending order, I had to convert to ascending.

 

[Domski]

Hi,

Looks fine to me assuming it should look like:

=lookup(A1-1,Worksheet2!A:A,Worksheet2!D:D)

Could always try:

=INDEX(Worksheet2!D:D,MATCH(A1-1,Worksheet2!A:A,0))

It wouldn't matter what order your data was in then.

Dom