Index match or collect

HI Experts,

I try to set up a formula for below table.

Blue Part is on another sheet, and I would like to show the correct awbif article is the same and "Name" is "Kerstin" in the yellow marked column "awb new sheet".

I tried a few versions with Index and Match or collect, but without success.

Would be great if you could help me here.

Thanks in advance

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try something like this...

=INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1)

Paul Newcome

We would use an IFERROR for that.

=IFERROR(INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1), "")

All comments

Paul Newcome

Try something like this...

=INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1)

Christine Menke

Thanks a lot @Paul Newcome

it works great, one more question if you don't mind.

If no awb available for the search criteria I would like to have the cell empty instead of invalid value.

What do I need to add?

Thanks

Paul Newcome

We would use an IFERROR for that.

=IFERROR(INDEX(COLLECT({Source Sheet AWB Column}, {Source Sheet Article Column}, @cell = [Article New Sheet]@row, {Source Sheet Name Column}, @cell = "Kerstin"), 1), "")

Christine Menke

Thanks a lot it's working :-)

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