INDEX/MATCH Function Trouble


Hi Community, I am not the best with formulas and having trouble.

I have a Master Data sheet that has all my information about what each chemistry needs to be tested.

I have my new sheet, where I want to be able to enter in my chemistry, then the sheet will pull information from the Master Sheet and fill it in, according to whatever chemistry has been entered in that first cell.

Here is a clip from my New Sheet, where I would want "Cal Kit" to be filled in automatically whenever I enter in a new chemistry.

And here is a clip from my Master Data. The "Cal Kit" column is the information I am trying to get pulled over, depending on my chemistry of course.

Every time I try that formula, I am getting a circular reference error. How do I get it to index the Cal Kit column in relation to the chem. So when I enter in a Chem it pulls what cal kit(s) are used. Would it be easier to index the cal kit column in relation to the part number?

I know this is a bit confusing and hard to describe, I am trying my best to make it as clear as possible! Thanks for any suggestions!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!