Return a value with Vlookup, then subtract the value from the cell above it

Question

I am trying to subtract one value from another in cells on a different sheet. I'm returning the first value using a vlookup

=VLOOKUP(Homepage!A2,Data!A:D,2,FALSE)

And I need to then subtract the value from the cell above it on that sheet (It's B6) from it.

At the moment I can only get it to subtract a static value like this:

=(VLOOKUP(Homepage!A2,Data!A:D,2,FALSE)-Data!B5)

Can anyone provide a simple formula?

Solution

You should be able to replace VLOOKUP with INDEX & MATCH, you can then repeat that to offset to the cell above using -1, on the MATCH for the row number for the subtraction.

Something like the below should work for you, please try and let me know,

=INDEX(Data!B:B,MATCH(Homepage!A2,Data!A:A,0),2)-INDEX(Data!B:B,MATCH(Homepage!A2,Data!A:A,0)-1,2)