VLOOKUP or INDEX MATCH Referencing Another Sheet

I would like to pull "Type" from a different sheet by matching the Project #. I've used both INDEX MATCH and VLOOKUP formulas which has worked well!

Here are the formulas:

=INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0))

=VLOOKUP([Project #]#, {Program Intake Form Range 2}, 5, false)

Problem Statement: But the issue is when I copy these formulas into another sheet, I am getting #INVALID REF error. I am able to fix this issue by editing "Reference Another Sheet" and manually re-selecting the columns from the other sheet.

Is there a way to write the formula, so that I would not need to manually Reference Another Sheet?

I have over 100 sheets (and this will grow over time) and I want to automate this formula.

Find more posts tagged with

Formulas

Automations

Sheet Summary

Accepted answers

Genevieve P.

Hi @Omid

Cross-sheet references, such as your {Program Intake Form Range 1}, are unique to each individual sheet where they are created.

This means that when you copy/paste a formula with a reference in it into an entirely new sheet, the reference is just plain text without being associated with another sheet or column. You will need to manually tie in the correct column to the referenced text each time, as you've been doing.

Cheers,

Genevieve

All comments

Andrée Starå

Hi @Omid

I hope you're well and safe!

You would have to use column-formulas in the other sheet for it to work.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Omid

@Andrée Starå - Hope you're having a great week! Thank you for your quick response!

By that, do you mean putting the formula in a cell within the sheet versus Sheet summary?

If so, I tried that by just copying "=INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0))" into Sheet #2 and I'm getting the same error. See below.

But When I go into sheet #2 and manually click "Reference Another Sheet" and select the reference sheet and reference column in the other sheet, then it works:

Wondering if there is a way to just copy this formula and paste it in Sheet #2, 3, 4, etc in an automated way?

Thank you in advance!!

Genevieve P.

Hi @Omid

Cross-sheet references, such as your {Program Intake Form Range 1}, are unique to each individual sheet where they are created.

Cheers,

Genevieve

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.