New Contributor

# Matching Values in formula with IF

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?

6 Replies

# Re: Matching Values in formula with IF

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

# Re: Matching Values in formula with IF

Hey Harun24HR,

I tried your method but it is still not functioning properly

# Re: Matching Values in formula with IF

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?

# Re: Matching Values in formula with IF

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