How can I make a multi-IF cross-sheet INDEX formula?

Hi there!

I've been stumped on trying to make the correct formula for my situation. Any help is greatly appreciated!

In one sheet (Data Collection sheet) I have a column for Sample ID and a column for Results. In another sheet that I'm using to report out, I have a column for the results of each possible sample ID. I want each row to be a lot #, with multiple samples being tested on each lot (hence the wide sheet with a column for each sample ID in the second sheet).

What I'm trying to create is a cross-sheet formula that I can put in the second one that says something along the lines of:

IF the Lot # at the row of this second sheet is the same as the Lot # in the Data Collection sheet,

THEN return the result value for Sample 1 in the correct column.

This same process needs to happen for a lot of columns (one for each sample).

When I was trying to do this I ran into trouble because the Sample ID column in the Data Collection sheet lists all the sample IDs, and in the second sheet there is a separate column for each Sample ID.

Anyone know how to do this?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!