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

Sophie Knudson

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?

Thanks!

AravindGP

Hi @Sophie Knudson

It would help if you can give screenshots with dummy values of the source sheet and destination sheet