Cross-Reference Formula Help with IF Statement

KKathol

Hoping someone can help me fix this formula:

=IF({Scope & Proposal Master List status column referenced} = "Won", INDEX({Scope & Proposal Master List project name column referenced}, MATCH([Project Name]@row, {Scope & Proposal Master List Status}, 0)))

I have two sheets. In the "destination" sheet (Project Wins) I have a column labeled Project Name. In that column, I want to populate the project names from the Project Name column in the source sheet (Scope & Proposal Master List) only if the Status column in the source sheet says "Won".

ericncarr

Hi @KKathol do you want to populate the Project Names on multiple rows or collect them all in one row?

If you want to collect them on multiple rows, I recommend creating a "#" helper row and numbering 1-x, x being however many Projects you want to capture.

Then do an index(collect()) from your destination sheet to your source sheet.

=INDEX(COLLECT({Project Name Range on Source Sheet}, {Range where "Won" might be present on source sheet}, "Won"), [#]@row)

You could add criteria as you go forward, such as date ranges, project types, etc.

KKathol

Thank you! The [#]@row part … Do I need to create a reference for that to my source sheet or does the formula just "know" where to pull from as long as the other information is accurate?

I'm trying it and it is saying Unparseable:

=INDEX(COLLECT({Scope & Proposal Master List Project Name}, {Scope & Proposal Master List Status}, "Won"), [#]@row)

KKathol

@ericncarr

Forgot to tag you in the comment above!

ericncarr

@KKathol the [#]@row you would add to your destination sheet where you want to pull in the Project Names. Putting numbers 1 - x in that column will basically just tell your index(collect()) which number in the index you've created you want to show on that line. If you add that in does it work?