Matching Values in formula with IF

New Contributor

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: 

Parth25_0-1660620415219.png

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:

Parth25_1-1660620829395.png

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?

6 Replies

@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),"")

 

 

Harun24HR_0-1660622781674.png

 

 

 

Hey Harun24HR,

I tried your method but it is still not functioning properly
It must work. I think problem is in your IF() function logic. All the IF() result is not less than 0.065. Can you share a dummy workbook with fake data and explain where it is not functioning properly?

Parth25_0-1660623944820.png

everytime i am trying to use the formula it is asking me to open a file from system. 

@Harun24HR 

Did you download my attached file and check?

@Harun24HR 

 

i am trying to share the file but it says the file type (.xlsx) is not supported