CODEWITHSUNDEEP
exceloffice-2010
Jeebwise
Jeebwise

Reputation: 177

Excel multi column lookup

I am unsure how to Google this one. I have a table that looks like the below

 Last Name | First Name | Team A | Team B | Team C
 Smith     | John       |    X   |        |
 Doe       | Jane       |        |   X    |

This would be the main sheet. The names in this sheet are divided into other sheet depending on what department they are in. Those sheets are setup in the same formats with the same columns. If the people in the main sheet are marked with an X in one of the columns I would like that same column marked in marked in the department sheets.

Upvotes: 0

Views: 59

Answers (1)

AxGryndr
AxGryndr

Reputation: 2344

Your best bet might be to create a hidden column A where the value is a combination of column B and column C on all of your tabs. You could then use the standard VLOOKUP wrapped in an IFERROR clause.

For example,

 =IFERROR(VLOOKUP(A1,Sheet1!A:F,3,False),"")

The IFERROR handles the instance that you may have a name on a sub tab not on the main tab. It returns blank instead of #N/A. The VLOOKUP is checking the value in A1 to what is in A1 on your main tab. A1 would be the combination of First and Last. The VLOOKUP would need to be in each of your team columns shifting the column returned number in each VLOOKUP.

Upvotes: 1

Related Questions