INDEX/MATCH Function Trouble

nroth

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!

SteyJ

https://community.smartsheet.com/discussion/117358/index-match-function-trouble

Would it be easier to reference part number instead of cal kit number: yes

by the looks of it, it looks like the [cal kit]@row column you are trying to match is blank. I recommend replacing it like this, assuming your Part Number@row is not blank:

=INDEX({Master Cal Kit}, MATCH([Part Number]@row, {Part Number Column}, 0))