Formula Help! VLOOKUP searching through a multi-select dropdown

I keep trying different variations and can't find it right:

The formula that I used which is close would be =VLOOKUP([Item ID Number]@row, {Adult Sponsor List Range 1}, 3, false).

Sheet 1: giving tree wish list

Sheet 2: the adult sign-up list

I want for the adult email address (listed in sheet 2) to be looked up and added into sheet 1. However, the items that adults can sign up for is a drop down multi-select.

The formula I listed above is working for Item ID 02a and 01a, but it won't work for the Item IDs that are multi-select (01b, 01c, 02b, 06b, 15a). I have also tried changing the formula's true/false statement. "True" for an approximate match will provide #NOMATCH, even for the ones that worked with the "false" exact match.

Find more posts tagged with

Sheets

Formulas

Accepted answers

DKazatsky2

Hi @Deena Duran,

Instead of a VLOOKUP, try an INDEX/COLLECT.

=IFERROR(INDEX(COLLECT({Adult Email}, {Item ID}, HAS(@cell, [Item ID Number]@row)), 1), "")

{Adult Email} is the "Adult Email" column from sheet 2.

{Item ID} is the "Item ID" column from sheet 2.

Hope this helps,

Dave

All comments

DKazatsky2

Hi @Deena Duran,

Instead of a VLOOKUP, try an INDEX/COLLECT.

=IFERROR(INDEX(COLLECT({Adult Email}, {Item ID}, HAS(@cell, [Item ID Number]@row)), 1), "")

{Adult Email} is the "Adult Email" column from sheet 2.

{Item ID} is the "Item ID" column from sheet 2.

Hope this helps,

Dave

Deena Duran

Hello Dave,

It worked perfectly! I don't want to sound dramatic, but since this is a sign-up list for giving gifts to children for Christmas, you essentially saved Christmas!

Thank you for your help, @DKazatsky2!

Deena

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.