Lookup the second last row in a table

[Cedicon]

Hi,

I have a table queried by SQL and has several columns containing prices for many previous days for many product-ID's.
On a another sheet (sheet1) I have the column A contains values (ID's) which are contained in the column A in the sql-table. Also on sheet1 i have a price for each ID (in column B let's say), and this price is the latest price achieved by a lookup in the sql-table, where i have used the following formula to obtain it:

=LOOKUP(2;1/(sql-table[ID]=A6);Sql-table[price]) - the table here is inversed as to obtain last row. A6 is a certain ID i wanna lookup.

Now my problem is that I want to calculate a % in change between second last price and last price. So i wondered if i could just subtract 1 from the above formula so as to move one row/price up...or is that an offset-function? ..please correct me if i'm wrong.

btw the above formula is one i have got inspiration to make from another formula i found on internet, and i actually don't quite understand the lookup_value "2" in it... can someone help? thank you a lot!

[Sadath31]

The below formula will return the previous row no.

LOOKUP(2;1/(sql-table[ID]=A6);row(Sql-table[price])-1)

[Cedicon]

The below formula will return the previous row no.

LOOKUP(2;1/(sql-table[ID]=A6);row(Sql-table[price])-1)

Thanks, yes but i want it to return the value which is in the price-column for that certain ID, here A6. I have lots of different ID's in the sql table and that id A6 is more or less appearing every 60 row but that is random and not really to control -when there's a price available for it for a particular day then it shows up in the table... i tried to write:

LOOKUP(2;1/(sql-table[ID]=A6);OFFSET(sql-table[price];-1;0))

which returns the 2nd last price but not for that particular ID A6, but for another ID..just the one above A6' price. so it just needs a kind of vlookup maybe...thanks