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))

Find more posts tagged with

Accepted answers

MichaelTCA

@ccarriger

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.

All comments

MichaelTCA

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?

ccarriger

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.

MichaelTCA

@ccarriger

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.

ccarriger

@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 :)

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.