Checking two conditions then populating a reference from another sheet

asd0723

Hi all, I am trying to cross reference between two sheets based on certain conditions. The two conditions on one sheet will then be checked against a master list to populate a column on the original sheet.

Specific example is as follows:

The original sheet has columns for the following

Assay Type* = PNG

Task = SAP

I want to cross reference the contents of those original columns across a master list which would have each assay type and task type equating to a certain method (looks like this):

Assay Type*: PNG, Task: SAP, Method: Prot2_SAPprep-BFAHAM

I would then want the method (Prot2_SAPprep-BFAHAM) on this master list to populate to a column in the row on the original sheet. This would continue for all the different combinations of Assay Types and tasks as they all would populate the specific method their combination entails.

Any help would be much appreciated! Thanks

Cory Page

Would you be able to create a very small and simple excel file with tabs representing your different sheets? A couple of rows of data in each would be really helpful, doesn't have to be fancy just enough so that its easier to build out the formula for you.

Georgie

Hi @asd0723,

To lookup one cell using multiple criteria, you can use INDEX with COLLECT. For more information on using these two functions together and information on creating cross-sheet references, take a look at the following help articles: 

• Formula combinations for cross sheet references
• Create cross-sheet references

You may also find this related Community thread helpful: 

• Index Match or Index Collect with multiple criteria.

So, your formula might look something like this: =INDEX(COLLECT({Method}, {Assay Type}, Assay@row, {Task}, Task@row), 1)

Hope that helps! If not, it would be great if we could see screenshots (with any sensitive data removed) of how your two sheets are set up.

Thanks,

Georgie