Complicated Vloopup, IF formula

Jennifer Flanagan

I have a complex formula and having trouble getting it right.

I have 2 sheets with contact information. One sheet - Summary All Projects Contact List - has all the contacts on a project. Each project has multiple contacts. There is "NC Main POC" contact noted per project via a checkbox on the "Summary All Projects Contact List" sheet. There may be more than 1, but usually 1. Another sheet lists all the projects- Summary All Projects. There is a column called NC Main POC. Right now all the cells are blank.

I want to reference the Summary All Projects Contact List, and find the NC Main POC per project as noted by the checkbox, and pull that contact information to the Summary All Projects sheet.

What is a formula that references the contact sheet to look up the NC Main POC as noted by the checkbox to populate the NC Main POC on the project sheet?

Can I use Index and Match to return the right value?

Javier Arbelaez

I like to use if, concatate ,and vlookup to solve this. I would use if to say if NC Main POC is checked, then 1, else 0. then combine that with the project name just the '+' symbol. This would be done on your contact sheet with the name of the contact coming after that. Then on your project sheet vlookup(projectname'+'1,within the sheet columns, returning the name of the employee). Hopefully that makes sense.

Jennifer Flanagan

Hi Javier,

Thanks for the quick reply. I am not familiar with those functions combined. Is it possible to write out the example?

Javier Arbelaez

In your contact sheet, BEFORE your contact name, I would have a column that says [Project Name]+IF([NC Main POC]=TRUE,1,0). This will combine your project name and your person with the NC Main POC checked off

In your project sheet use Vlookup([Project Name]+1,'Select your sheet and the 2 columns that you need (the formula column we selected and the contact name next to it),2,FALSE)

Hopefully that helps