Aug 15 2022 08:42 PM
I am a Jewellery Business owner and it is important for me to manage my inventory at the same time keeping a track of my rates as there are lots of variables that come in to play....
My problem is that I am trying to match values in two sperate sheets to get the correct rates. For example:
Column "K" is studded items in my jewellery "L" is the weight "M" is number of pieces "N" is studded rate per carat. Now the rates are decided by the weight of studded item so I used =IF(L:L/M:M<=0.065,"28000","") to get the correct answer which definitely worked but,
Instead of using this formula string I want to match the values in column "K" and "N" so that I just have to change values in this sheet without disturbing my formula. My data is in sheet 2 of the same workbook:
I tried using this with IF formula to match column values in both the sheet to get the correct formula but it did not work. What should I use instead?
Aug 15 2022 09:04 PM - edited Aug 15 2022 09:10 PM
@Parth25 VLOOKUP(), INDEX/MATCH(), FILTER(), XLOOKUP() all these function should work for your case. I would recommend to use XLOOKUP() function. See the attached file.
=IF(L2:L4/M2:M4<=0.065,XLOOKUP(K2:K4,Sheet2!A:A,Sheet2!B:B,"",0),"")
Aug 15 2022 09:12 PM
Aug 15 2022 09:16 PM
Aug 15 2022 09:26 PM
Aug 15 2022 09:32 PM
Aug 16 2022 01:02 AM