Index/Match with multiple criteria - returning a date

Hi,

I am trying to return a date from one sheet to another based on 2 criteria that must match.

I have columns COUNTRY, PROJECT TYPE and DATE on both sheets and want to return the Date from the source sheet onto the second sheet based on matching criteria Country and Project type. Is there a way to do that?

Been looking for a solution for my case in the existing chats, but did not seem to find a current answer that works for me.

Thank you in advnce.

Find more posts tagged with

Formulas

functionality

Cell linking

Accepted answers

Andrée Starå

Hi Kristina,

I’d recommend creating a so-called helper column and use the JOIN function to join the column values together and then reference that column in the INDEX/MATCH formula.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi Kristina,

I’d recommend creating a so-called helper column and use the JOIN function to join the column values together and then reference that column in the INDEX/MATCH formula.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

A JOIN/COLLECT may also work. Something along the lines of...

=JOIN(COLLECT({Other Sheet Date Column}, {Other Sheet Country Column}, [Country Column]@row, {Other Sheet Project Type Column}, [Project Type Column]@row))

Kristina Wichmann

@Andrée Starå - this worked, however I wanted to avoid that extra column. please keep me in the loop if any improved functions is being released.

@Paul Newcome - Thank you also for your suggestion, I did try and it returned the Date including the time 6 times :). Could not quite go with that, but it looks like this function combination offers some tricks I yet have to play with.

Thank you both for the fast responses! 👍️

Paul Newcome

@Kristina Wichmann My apologies. I didn't realize each was listed multiple times for the country/project type column.

If it is listed multiple times, are you wanting the most recent?

You said "Date including times"... Are you pulling from a system generated column and only want the date? We should be able to incorporate a DATEONLY function to separate this value.

Are you able to provide a screenshot of the sheets with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed so that we can better visualize exactly what you are trying to accomplish?

Kristina Wichmann

@Paul Newcome For now I will go with the "Helper column" and only come back to this conversation if necessary. Thanks again.

Paul Newcome

Sure thing. Just trying to help do away with the helper column you were wanting to avoid. INDEX/COLLECT may even be a possibility.

Andrée Starå

@Kristina Wichmann

Excellent!

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.