Index match on multiple sheets

chhunt

Apologies if this question has been asked, I wasnt sure how to search for this within the community.

I stumbled across this formula using the AI formula builder which sort works but not sure how to fix it, its when the simple formulas start stacking is when im getting lost in how they work together.

What I have is an Intake sheet with a Unique Project ID column and a column for an engineers contact name which lives in workspace1. There is a column formula on the engineers column that should look on a sheet2 in workspace2 and if the project id/contact name is there, list the engineers name, if it is blank leave it blank or if it doesnt exist, look at the sheet3 in workspace3 and enter it or leave it blank.

This formula seems to work only if the project id exists on both sheets. The project ID has the potential to be in both sheets, but could only exist in one of them.

Column Formula applied to the Framework Network Engineers Column on the Intake Sheet:

=IFERROR(IF(ISBLANK(INDEX({Implementation Metrics | FNE}, MATCH([Project ID]@row, {Implementation Metrics | Project ID}, 0))), INDEX(COLLECT({Network Metrics | FNE}, {Network Metrics | Project ID}, [Project ID]@row), 1), INDEX({Implementation Metrics | FNE}, MATCH([Project ID]@row, {Implementation Metrics | Project ID}, 0))), "")

Intake Sheet:

Network Metrics (above formula only works if SM-926 exists in the other sheet):

Implementation Metrics (SM-926 does not exist):

Gillian C

Hi @chhunt

When you say the formula isn't working, what error message is being returned? Could I also clarify, if the project ID appears in workspace 2 it won't be in workspace 3?

chhunt

Thanks for the reply Gillian.

The formula isnt providing an error message (this could be due to the formula leaving it blank if there is an error, I think thats how im reading it). In the screenshots above, the first one is where the formula lives and I would expect it to list the contact name from the second screenshot, but since the project id doesnt exist in the third screenshot, its not populating the first one.

The formula will populate the cell in the intake sheet only if the Project ID is in both the implementation and network sheet. Our Network and Implementation teams do different things which is why we have the multiple sheets, but there are times where they both work on the same project and this column is for a hybrid user that exists between both groups.

I hope this answers your question and clears things up.

Gillian C

Hi @chhunt

I think so.

Try this formula (putting your own column titles etc in)

=IF(Engineer@row <> "", Engineer@row, IF(IFERROR(INDEX({Sheet 2 FNE}, MATCH([Project ID]@row, {Sheet 2 Project ID}, 0)), "NM") <> "NM", INDEX({Sheet 2 FNE}, MATCH([Project ID]@row, {Sheet 2 Project ID}, 0)), IF(IFERROR(INDEX({Sheet 3 FNE}, MATCH([Project ID]@row, {Sheet 3 Project ID}, 0)), "NM") <> "NM", INDEX({Sheet 3 FNE}, MATCH([Project ID]@row, {Sheet 3 Project ID}, 0)), "")))

This is based on the following screengrabs

You can't have a formula in a cell and have a manual entry so basically the formula above says if the Engineer column in Sheet 1 is not empty, return the Engineer column value, if it is empty check if there is an Engineer listed in sheet 2 for the Project ID. If there is no Project ID there will be a #No Match, so the IFERROR returns "NM" in this case…this is important because if the Project ID is there but they just haven't assigned someone yet then it would be a blank. So if there is a Project ID it will return that value (if it is blank it will return a blank cell). If there is no Project ID on Sheet 2 it will go and look for the Project ID in Sheet 3 with the same conditions as above.

If no one has been assigned yet and you want this highlighted you could modify the formula to something like

=IF(Engineer@row <> "", Engineer@row, IF(IFERROR(INDEX({Sheet 2 FNE}, MATCH([Project ID]@row, {Sheet 2 Project ID}, 0)), "NM") <> "NM", INDEX({Sheet 2 FNE}, MATCH([Project ID]@row, {Sheet 2 Project ID}, 0)), IF(IFERROR(INDEX({Sheet 3 FNE}, MATCH([Project ID]@row, {Sheet 3 Project ID}, 0)), "NM") <> "NM", INDEX({Sheet 3 FNE}, MATCH([Project ID]@row, {Sheet 3 Project ID}, 0)), "Not Assigned Yet")))

Hope that helps? If I've missed something or picked something up incorrectly, just @ me in the reply :)