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}, "")))
Best Answer
-
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)), "")
Answers
-
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)), "")
-
thanks Paul - it works on my initial test!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.2K Get Help
- 452 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!