Need formula help

I'm trying to match info in another sheet and return a date column to the first sheet. I've tried a few things but keep getting errors.

In Sheet A each row is a job number. I want to fill in the job completion date from Sheet B. It has many job number rows with different job types. The Job type column is Parent/Child. I'm trying to lookup text in the Job type column in sheet B (it will be a child), and return the completion date where the Job number in Sheet B matches Sheet A.

I think maybe the parent/child thing is my problem, but not sure how to solve it. Any help would be much appreciated!

Find more posts tagged with

Sheets

Formulas

Accepted answers

Paul Newcome

Try an INDEX/COLLECT.

=INDEX(COLLECT({Date Column}, {Job Number Column}, @cell = [Job Number]@row, {Job Type Column}, @cell = "Charlie2"), 1)

Paul Newcome

Happy to help. 👍️

Now that we know the base formula works, we can wrap it in an IFERROR to tidy up those other rows.

=IFERROR(INDEX(COLLECT({Date Column}, {Job Number Column}, @cell = [Job Number]@row, {Job Type Column}, @cell = "Charlie2"), 1), "")

I tend to leave that off until we have a working formula because it can mask errors that would aid in troubleshooting.

All comments

Paul Newcome

Try an INDEX/COLLECT.

=INDEX(COLLECT({Date Column}, {Job Number Column}, @cell = [Job Number]@row, {Job Type Column}, @cell = "Charlie2"), 1)

scewing

Thanks for your reply, Paul.

I gave it a shot and I get #INVALID VALUE. I double checked that the columns are the same data types.

There are some blanks for dates and some blanks for the Job numbers in Sheet B. I'm not sure if that would cause this error.

Paul Newcome

Can you provide a screenshot of a row that is throwing that error but should be pulling something over?

scewing

I figured out the problem! Your formula was spot on! Thanks so much! I had blanks for Job number in sheet B. There are hundreds of rows and I just didn't scroll down far enough to see that it was pulling in dates properly. It's just that the first few hundred had blanks for Job Number so it threw that error for those.

Thanks again!

Paul Newcome

Happy to help. 👍️

Now that we know the base formula works, we can wrap it in an IFERROR to tidy up those other rows.

=IFERROR(INDEX(COLLECT({Date Column}, {Job Number Column}, @cell = [Job Number]@row, {Job Type Column}, @cell = "Charlie2"), 1), "")

I tend to leave that off until we have a working formula because it can mask errors that would aid in troubleshooting.

scewing

Fantastic! Thank you again!

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.