Formula for lookup against concatenate

looking to populate a date from another sheet if a match of the unique ID is found on the other sheet.

So sheet A has date column and unique ID column. Sheet B has the same unique ID column but the date is populated in the date column. So I want to bring the date into into Sheet A if a match of the unique ID exists. For further context, when the date column on sheet A is populated with any value, the row will be sent to another sheet, archived basically. I have created the concatenate in it's own column, but I also know that I could do it within the same formula and avoid the extra column.

Not sure if this is just a vlookup or a combination if and match or something?

Tried the following with an incorrect argument error:

=VLOOKUP({unique Id Concat}, [Dist name+Branch Name+Dist Cust Acct #]@row, {Date sent to ITN})

also tried this one with the invalid column value error.

=IF(COUNTIFS({unique Id Concat}, [Dist name+Branch Name+Dist Cust Acct #]@row), {Date sent to ITN}, 0)

Find more posts tagged with

Accepted answers

Paul Newcome

Once you confirm the INDEX/MATCH is working as expected, you can wrap it in an IFERROR to replace errors with blanks. I don't normally do that right away because it needs to be removed for trouble shooting if it isn't working the first try.

=IFERROR(index/match, "")

All comments

Paul Newcome

Have you looked into an INDEX/MATCH?

=INDEX({Date Column}, MATCH([ID Column]@row, {ID Column}, 0))

Nate@E

thanks Paul. Yes that works, but for those that there is no match, it populates "no match" and I need it to be blank until a date is populated so that the automation doesn't pick it up and archive the row. Can it be adjusted slightly?

I was able to accomplish this with Datamesh, but since I'm just helping a different department figure this out, I'd rather not have the datamesh config be only under me. I guess I could transfer ownership of the config, but probably only if they have a license.

Paul Newcome

=IFERROR(index/match, "")

Nate@E

thanks!

Paul Newcome

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