Project Intake Sheet and Project Metadata Error

JeffV

We are trying to get two added columns from the project intake sheet to connect to the project metadata sheet. Columns names are Business Opportunity and Project Score. We have added the headers and proper formulas to row 1 of each sheet. However, the new columns are showing an "#Invalid Value" in each.

I have provided screen shots and formulas for each below.

Project Metadata sheet:

Project Intake Sheet:

Any help on the error would be greatly appreciated.

AravindGP

Hi @JeffV

Your index is the column from which you need the data. So, your formula should be =INDEX({Business Opportunity column reference}, MATCH([Project ID]@row, {Project ID column reference}, 0) for Business opportunity. Replace the business opportunity column reference in the formula with the project score column reference for the project score column's formula.

JeffV

Appreciate quick response and updated formula but still getting same error:

Note that in the other columns, for instance "Target Start Date" this formula is connecting the data properly:

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Target Start Date]$1, {Portfolio Summary Header Row}, 0))

Genevieve P.

Hi @JeffV

If looks like you may have copy/pasted the formula from the response above versus creating a new {reference} looking at the Business Opportunity column, is that correct?

The INDEX(MATCH formula suggested by Aravind uses individual column references to find the row/cell to bring back.

However the formula you have written here - INDEX(MATCH(MATCH - uses an entire sheet reference to look across the whole other sheet, then find the column and the row based on the top-row title.

If you've added in a new column, double check that your reference: {Portfolio Summary Data} is looking at that new column as well! You may need to click edit reference and re-select the range across the sheet to include new columns.

Here's more information: Create cross sheet references to work with data in another sheet

Cheers,

Genevieve