Vlookup to look at one sheet and then if matching another cell to show difference in amoun

[fuzioneer]

I have two sheets which are price lists, some prices have changed and some products have been removed.
I want to do a Vlookup to check if the product still available in new 2019 sheet and then have a column which shows percentage and amount changed by. HELP needed as beyond my feeble excel skills ?

[PaulM100]

Without a sample, i will guess the data columns. For availability in C2(B column contains prices in both sheets:
=if(iserror(vlookup(a2,sheet2!$a$2:$a$100,1,0)),"not available","available")
for percentage:
=if(C2="Available",if(B2-vlookup(a2,sheet2!$a$2:$B$100,2,0)<0,"decrease by "&abs(B2-vlookup(a2,sheet2!$a$2:$B$100,2,0)),"Increase by "&abs(B2-vlookup(a2,sheet2!$a$2:$B$100,2,0))),"")
for percentage: =iferror(B2/vlookup(a2,sheet2!$a$2:$B$100,2,0),"")

[fuzioneer]

thanks for the quick reply Paul, the columns in question are A contains the Product Number which is the same in each sheet and Column C contains the prices.

[fuzioneer]

NoNo. Description Trade ex. VAT Retail inc. VAT

10001 PRODUCT 1 90.00 154.29
10002 PRODUCT 2 127.50 218.57
10003 PRODUCT 3 127.50 218.57
10004 PRODUCT 4 174.10 298.46

4 Columns as shown above, with over 5000 rows

[PaulM100]

So, change the references in my formulas or upload a sample

[fuzioneer]

I have added a sample file with the two sheets, sorry tried to use your formulae and cant seem to make it work.

When I said a product is available or not, I mean that in one sheet a product exists but in this years price sheet it doesn't as it has been discontinued so its not a text search, its a match against Product No.

[PaulM100]

See attached

[fuzioneer]

Thanks so much Paul, its all working now except percentage change which is showing very high figures >90% even on products that are only changed by small amounts, any ideas ?

[fuzioneer]

sussed it, i needed to do 1-the column to get the actual percentage