Need to bring data from another sheet based on 2 fields matching

The formula below must be mising something that I can't quite get. Goal: If the 2 cells combo of the row data on the current sheet are found on either of the other two sheets, then bring back the data from the request ID column from the match found on the other two sheets, or blank.

=IF(OR(COUNTIFS({Sheet1 - E#}, [E #]@row, {Sheet1 - A/R}, [Vendor A/R #]@row >0, COUNTIFS({sheet2 E#}, [E #]@row, {sheet2 A/R}, [A/R #]@row >0) {requestID}, "")))

Find more posts tagged with

Formulas

IF(

Accepted answers

Paul Newcome

Try this instead:

=IFERROR(IFERROR(INDEX(COLLECT({Sheet 1 Request ID}, {Sheet 1 - E#}, @cell = [E #]@row, {Sheet 1 - A/R}, @cell = [Vendor A/R]@row), 1), INDEX(COLLECT({Sheet 2 Request ID}, {Sheet 2 - E#}, @cell = [E #]@row, {Sheet 2 - A/R}, @cell = [Vendor A/R]@row), 1)), "")

All comments

Paul Newcome

Try this instead:

Nate@E

thanks Paul - it works on my initial test!

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.