Return a value based on multiple criteria in two sheets

I am looking for formula which returns a value from a single column based on multiple criteria in two sheets. So from source sheet I have a due date column I want to return to destination sheet based on the following criteria.

Destination sheet - needs Due date based on

State, Grade Band, Grade, Topic #

Source sheet has the due date

Pull this date into Destination sheet based on the matching State, Grade, Grade Band, Topic #

I started using this formula but it was incorrect

=INDEX(COLLECT({course link due date}, State@row, [Grade Band]@row, Grade@row, [Topic #]@row, 1))

Destination Sheet

Source Sheet

Find more posts tagged with

Accepted answers

Paul Newcome

And how is the date being populated in the source sheet?

All comments

DKazatsky2

Hi @Beth Klineman,

I see a couple of things wrong here.

First: The syntax of your Index/Collect formula is off. There needs to be a range and criteria for each check, you appear to only have the criteria part. Here is what the formula should look like.

=INDEX(COLLECT({course link due date}, {Source State}, State@row, {Source Grade Band}, [Grade Band]@row, {Source Grade}, Grade@row, {Source Topic}, [Topic #]@row), 1)

Notice the additional ranges {Source State}, {Source Grade Band}, {Source Grade}, and {Source Topic}.

Second: The values in "Grade" and "Topic #" seem to have different formats between the 2 sheets. This will cause you to never have a match and may result in an #INVALID VALUE error.

Hope this helps,

Dave

Beth Klineman

Thank you, Dave (@DKazatsky2) — I still get a invalid ref error.

I updated the sheet to match the data in both columns - thank you for catching that and I used your formula

=INDEX(COLLECT({course link due date}, {Source State}, State@row, {Source Grade Band}, [Grade Band]@row, {Source Grade}, Grade@row, {Source Topic}, [Topic #]@row), 1)

Destination sheet (due date column is a date column in both sheets and Grade and Grade Band columns are drop-downs in both sheets

Source sheet

Paul Newcome

Make sure you are properly creating each {Cross Sheet Reference}. This article should help you get started:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Beth Klineman

@Paul Newcome I'm not 100% sure what you mean. For the Source cross sheet reference all references were added and now the error says incorrect value. The references below are all pointing to the source sheet and each column has the same data type for the source and destination columns…the only thing different is on the source sheet Topic# is the primary column but still the same data type

Paul Newcome

That was in response to the INVALD REFERENCE error you had mentioned in your previous post.

Have you confirmed that both the source for {course link due date} and the column you are putting the formula in are both formatted as date type columns?

Beth Klineman

@Paul Newcome yes both columns are date type column

Source sheet

Destination sheet

Paul Newcome

And how is the date being populated in the source sheet?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.