Lookup a value in 1 of 3 sheets

kpl70sx · 09/22/22

Hello,

I've created a Project Sheet that contains tasks from 3 different departments. Each department has it's own master sheet. Each master can be as many as 2500 rows and they all have identical column configuration.

I'm trying to retrieve info for the tasks in my project from these department sheets. A task may be in one, but only one of the department sheets - A or B or C. In the logic below, I am looking for the Task Status which is in column 4 in each of the department master sheets.

I've listed them in order of likelihood of the task being in a specific sheet. (90% of my project's tasks will be in department A)

The following is my logic:

1) Check for Task ID in Dept A Tasks

o If found, use it =VLOOKUP([Task ID]@row, {Dept A Tasks Range 1}, 4, 0)

o If not found:

2) Check for Task ID in Dept B Tasks

o If found, use it =VLOOKUP([Task ID]@row, {Dept B Tasks Range 1}, 4, 0)

o If not found:

3) Check for Task ID in Dept C Tasks

o If found, use it =VLOOKUP([Task ID]@row, {Dept C Tasks Range 1}, 4, 0)

I'm not sure how to construct this and whether I should be using "=IF" or "=IFERROR" or even "=INDEX/MATCH".

Any help would be appreciated. Thanks, Kevin

Scott Peters · 09/23/22

Hi Kevin - We do this today with our Stakeholder registers. We have a master sheet of internal company contacts and a master sheet of external contacts. We have index/match columns for location, dept., title, mobile phone #, etc.

We have a selection field for "company" to indicate if it should be found in sheet A or sheet B. If you know which sheet the content will be in that's one way and would be easiest:

'=if(SourceSheet = "A", <your sheetA index/match formula>, if(SourceSheet = "B", <your sheetB index/match formula>, if(SourceSheet="C", <your sheetC index/match formula>, "No Data")))

If to you do NOT know which sheet to find it in, you would use index/match inside iserror inside a nested if. That will make a for a pretty long formula, so you might be better off creating 3 helper columns, one for each source sheet

'=if(iserror(index(columnyouwantinsheetA, match(taskid@row, [sheetAcolumn]:[sheetAcolumn]))), "No Data", index(columnyouwantinsheetA, match(taskid@row, [sheetAcolumn]:[sheetAcolumn]))

Repeat for helper column B / Sheet B and helper column C / Sheet C

Then, have the field you're actually trying to populate grab whichever helper column returns what you're looking for

Hope that helps?

Scott

kpl70sx · 09/23/22

Scott,

Thanks for your help.

Since I do not know which file my Task ID will be in, I'll be looking at using your second suggestion and will be using helper columns.

This gives me a lot to go on and will also give me more experience in setting up a fairly complex (for me) formula.

Thanks again. I really appreciate you taking the time to help!

Kevin

Scott Peters · 09/23/22