Formula Help! VLOOKUP searching through a multi-select dropdown

Deena Duran

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.

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

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