Cross-reference formula comparing 2 cells to return a 3rd
I've been trying all the suggestions from the formula handbook and the community but I'm still doing something wrong. Basically, I have 2 sheets. The 1st sheet needs to have a column that returns a value from the 2nd sheet. But it should only return that value if the email address and item number match between the two sheets.
My latest tries are below...any advice?
=INDEX(COLLECT({Second Sheet Range 4}, {Second Sheet Range 2}, [Email Address]@row, {Second Sheet Range 3}, [Item Number]@row), 1)
=JOIN(COLLECT({Second Sheet Range 4}, {Second Sheet Range 2}, [Email Address]@row, {Second Sheet Range 3}, [Item Number]@row))
Best Answer
-
Since your ranges have { } around the column names, the amount of rows wouldn't matter.
Have you tried removing one of the criteria and getting a return value? Even though the COLLECT function is commonly used for multiple criteria, you can still use a single criteria if you wanted.
Check each criteria individually to make sure you're getting a value.
Answers
-
Hello @ccarriger
The first INDEX/COLLECT function looks like it would work.
Are there any discrepancies in the column properties? Like the email address is a text but the lookup range are contacts?
-
Thanks, @MichaelTCA! I checked and they are all text/number fields so that's not a factor thankfully. Would it make a difference that my second sheet is significantly shorter than the first sheet? I assume that shouldn't matter, but sheet 1 has about 10k rows and sheet 2 has less than 4k.
-
Since your ranges have { } around the column names, the amount of rows wouldn't matter.
Have you tried removing one of the criteria and getting a return value? Even though the COLLECT function is commonly used for multiple criteria, you can still use a single criteria if you wanted.
Check each criteria individually to make sure you're getting a value.
-
@MichaelTCA, it ended up working on a different sheet so no idea if I had something wrong on the original sheet, but it's working now! Thanks for your help :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!