=INDEX MATCH .. formula

Trying to create a standardized process for setting up our construction sheet, contract, and change orders. I would like to be able to pull the client's contact info from one sheet ('Sheet Names') and have that information auto-populate back onto it's specific construction sheet, based on the project's address which will be typed into a cell. I don't even know if I explained that right or if INDEX/MATCH formula is the best plan of action for this... I tried a call with support and an hour later; I still cannot get it to work.

I would like the formula to refer to the entire column so I can 'file save as new' this sheet again and again on different addresses/projects.

=INDEX({sheet names Range 1}, MATCH(Address@row), {sheet names Range 4},)

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Malorie Scaramozi

It sounds like an INDEX(MATCH will work for you, as long as the address is exactly the same in both sheets!

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

You'll notice that the Match requires two values separated by a comma, instead of how you have a ) between the Address and {range}.

Your syntax is just a little off, try this:

=INDEX({sheet names Range 1}, MATCH(Address@row, {sheet names Range 4}, 0))

Let me know if this works for you!

Cheers,

Genevieve

SmartSheetUser01

This formula worked for me as I'm returning a specific date column but I also want to add one month to this formula results? Can you please help? Thanks!

=INDEX([Estimated Completion Date]1:[Estimated Completion Date]5, MATCH([Task Name]@row, [Task Name]1:[Task Name]5, 0))

Genevieve P.

Hey @SmartSheetUser01

The easiest way to do this is to simply add 30 days to the end result:

=INDEX([Estimated Completion Date]1:[Estimated Completion Date]5, MATCH([Task Name]@row, [Task Name]1:[Task Name]5, 0)) + 30

Would that work for you?

SmartSheetUser01

Awesome - it works! thanks as always!

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.