Conditional INDEX/MATCH Question

Alexandra Prescott

Hi there,

I am using the following formula to pull data from a second sheet but realized when "Medicine" is selected in the Entity/Dept column, I would need to match information from a Division column instead.

=INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0))

I thought a conditional Index/Match might help so I tried this but received an unparseable error:

=INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}), IF(JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, MATCH([JHU Department of Medicine Division]@row, {Division}, 0))

The column headers are below:

Could someone point me in the right direction?

Thanks,

Ali

David Tutwiler

I think you just need to move where the condition is coming from. You first need to do the IF, and then have 2 different INDEX/MATCH formulas based on the return. This should work:

=IF([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, INDEX({Notice Name}, MATCH([JHU Department of Medicine Division]@row, {Division}, 0)), INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)))

David Tutwiler

I think you just need to move where the condition is coming from. You first need to do the IF, and then have 2 different INDEX/MATCH formulas based on the return. This should work:

=IF([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, INDEX({Notice Name}, MATCH([JHU Department of Medicine Division]@row, {Division}, 0)), INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)))

Alexandra Prescott

That worked great. Thank you!

David Tutwiler

No problem, glad it is all working