Help with an INDEX(MATCH(MATCH( Formula

Hey all,

I have two sheets: Business Consult and Project Proposal. I'd like to move specific info from that sheet onto the Project Proposal sheet using a unique identifier, the Business Consult ID. I've mirrored columns on both sheets and they are in the same order (though Project Proposal sheet has additional columns). My first row on both sheets has column names. So basically, I want the formula to match the Business Consult ID on both forms, and output the value in a specific column based on the value in that same column on the source form.

Here is what I have:

=INDEX({ASD OIP Business Consult Intake Sheet Range 3}, MATCH($[Business Consult ID]@row , {Business Consult ID}, 0), MATCH([Consult: Projects 101 Review Verification]$1, {{ASD OIP Business Consult Intake Sheet Range 4}, 0))

Range 3 = all the columns in the Business Consult sheet

Range 4 = header row (1) in the Business Consult sheet

Where am I going wrong? I'm trying to model off of the formula built into the Metadata sheet that comes with the Project Management Office bundle.

Thank you!

Find more posts tagged with

Formulas

INDEX

MATCH

Accepted answers

deliciousfrog

The error is in the last MATCH function. The {{...}} syntax is incorrect and the reference to the header row needs to be an explicit cross-reference to that specific row.

I suggest you edit it to this formula:
=INDEX({ASD OIP Business Consult Intake Sheet Range 3}, MATCH($[Business Consult ID]@row , {Business Consult ID}, 0), MATCH([Current Column Name]$1, {ASD OIP Business Consult Intake Sheet Range 4}, 0))

Paul Newcome

You can click on the row number to select the entire row the same way you can click on a column header to select an entire column.

All comments

deliciousfrog

The error is in the last MATCH function. The {{...}} syntax is incorrect and the reference to the header row needs to be an explicit cross-reference to that specific row.

n.oconnor

Thanks for catching that double bracket! How do I explicitly reference that header row? Ensure the entire row is selected when I do the cross-reference?

Paul Newcome

You can click on the row number to select the entire row the same way you can click on a column header to select an entire column.

n.oconnor

Great, thanks Paul!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.