How can I build a formula to pull data from one sheet to another based on meeting two criteria?

K Miller

I have a sheet that contains 5 columns of data (My main sheet to pull data from). On a second sheet, I have a cell that gets manually populated with the name of a "Data Reviewer". On this second sheet, I want to pull the Client Name from my main sheet where the Data Reviewer on the main sheet matches the Data Reviewer that was populated on the second sheet and where Client # on the main sheet is Client #2.

I've tried IF(INDEX(MATCH. I've tried INDEX(COLLECT). And I've tried everything else I can think of......

Here is the main sheet:

Here is the second sheet:

So, I'm trying to create a formula in the light purple cell on the second sheet that will pull in the Client Name from the main sheet where the Data Reviewer matches the highlighted yellow cell on the second sheet and the Client # on the main sheet is Client #1.

Alex Carter

Maybe use a VLOOKUP([AMS Preview Testing]15, "reference to another sheet"[Client #:AMS Client ID], 3, FALSE)?

K Miller

Unfortunately this doesn't work as I need to formula to pull the Client Name from the main sheet where the Data Reviewer on the Second Sheet matches the Data Reviewer on the main sheet and where the Client # on the main sheet = "Client #2".

K Miller

I have two formulas that will pull the Client Name when only matching on one criteria. I want to be able to combine these two formulas together to pull back Client Name where both criteria match.

Formula one: =INDEX({Client_Name}, MATCH([AMS Preview Testing]60, {Data_Reviewer}, 0))

Formula two: =INDEX({Client_Name}, MATCH("Client #3", {Client #}, 0))