Cross Reference Formula, Matching Two Conditions (with Multi-Select Dropdown)

aschneiderheinze1025

Sheet 1 collects form responses for each new person added to our instructional team. First, they are asked if they are adding a vendor or individual. If the choose Vendor, the enter the [Vendor Name], and if individual, they select all of the options for [Role] that apply: developer, facilitator, instructor, mentor, and/or subject matter expert.

If vendor, they enter [Vendor Name[, and if individual they enter [First Name] and [Last Name], which I combine in another column [Full Name]. Whether they are vendor or individual, the form requires they enter [Street Address].

Sheet 2 is used to collect payment requests for vendors and individuals (who performed specific roles). Multiple payment requests can be submitted for an individual for different roles (e.g., one payment request for work as a developer, one for work as an instructor, etc.). They have to be separated for payment processing.

In the payment request form, they choose if they are submitting a request for a vendor or individual, and if they choose individual, they select ONE option from the dropdown: developer, facilitator, instructor, mentor, and/or subject matter expert.

For the vendor, they enter [Vendor Name] and for individual [First Name] and [Last Name], which I combine in another column [Payee (Full Name)].

I want to autofill other columns, including [Street Address]. To do this, I have to match two conditions:

1. [Payee (Full Name) in Sheet 2 must match [Full Name] in Sheet 1. (I refer to [Full Name] in Sheet 1 as {InstructorDirectory-FullName}.
2. [Role] in Sheet 2 must be one of the options selected in [Role] in Sheet 1, which again was a multi-select dropdown.

If these are TRUE, it returns [Street Address] from Sheet 1, which I refer to as {InstructorDirectory-StreetAddress}. If the Full Name isn't found AND/OR the role selected in Sheet 2 isn't one of the options selected in Sheet 1, "No Match" is returned.

I've tried all combinations of INDEX, MATCH, JOIN, COLLECT, and CONTAINS and I am not getting the Street Address to appear correctly. It appears if the Role in Sheet 2 is the ONLY role selected in Sheet 1, but I get "No Match" if its not the only role selected. In some cases, I even get a blank cell (not even "No Match") but I can't figure out after doing some testing why that's happening.

I think the core issue is being able to determine if [Role] in Sheet 2 (a non-multi-select dropdown) is one of the options selected in [Role] in Sheet 1 (a multi-select dropdown).

Some I've tried:

=IFERROR(IF(COUNTIFS({InstructorDirectory-FullName}, [Payee (Full Name)]@row, {InstructorDirectory-Role}, Role@row) > 0, INDEX(COLLECT({InstructorDirectory-StreetAddress}, {InstructorDirectory-FullName}, [Payee (Full Name)]@row, {InstructorDirectory-Role}, Role@row), 1), ""), "No Match")

I get a blank.

=IFERROR(IF(ISNUMBER(FIND([Role]@row, JOIN({InstructorDirectory-Role}, ", "))), INDEX(COLLECT({InstructorDirectory-StreetAddress}, {InstructorDirectory-FullName}, [Payee (Full Name)]@row, {InstructorDirectory-Role}, [Role]@row), 1), ""), "No Match")

I get "No Match" when the [Role} in Sheet 2 is one of the options selected in Sheet 1 but I get the Street Address if the [Role} in Sheet 2 is the only option selected in Sheet 1.

Any suggestions how to better handle finding if one of the options in a multi-select dropdown box is selected?

Kelly Moore

Hey @aschneiderheinze1025

Try this:

=IF(AND([Payee Full Name]@row <> "", Role@row <> ""), IFERROR(INDEX(COLLECT({InstructorDirectory-StreetAddress}, {InstructorDirectory-Full Name}, [Payee Full Name]@row,{InstructorDirectory-Role}, CONTAINS(Role@row, @cell)), 1), "No Match"))

Will this work for you?
Kelly

aschneiderheinze1025

Works perfectly! I was on the right track at least. Thanks so much! :)

Kelly Moore

Hey @aschneiderheinze1025

Try this:

=IF(AND([Payee Full Name]@row <> "", Role@row <> ""), IFERROR(INDEX(COLLECT({InstructorDirectory-StreetAddress}, {InstructorDirectory-Full Name}, [Payee Full Name]@row,{InstructorDirectory-Role}, CONTAINS(Role@row, @cell)), 1), "No Match"))

Will this work for you?
Kelly

aschneiderheinze1025

Works perfectly! I was on the right track at least. Thanks so much! :)