1

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?

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.