VLOOKUP on a Multi DropDown List

System

This discussion was created from comments split from: VLOOKUP on a Multi DropDown List.

ChristineMesa

@Bassam Khalil I have a similar issue I am trying to solve and have not yet.

I am working in a test sheet so happy to share.

2024 Case Worker & Foster Family Birthday Requests: For context this is where people can request a birthday bag be made for foster kids. Once the bag is made it is put in a slot for pick-up. Sometimes the toys are big and need multiple slots so this is a multiple select field. Volunteers make the bags so multi select is the easiest to have for them. I have a drop down with numbers 1-468.

Then there is Completed bag inventory slot management sheet. This is a list of the 468 slots/rows and I want a consolidated sheet that can show slot availability so we can check and make sure we are not running out of room. For slot availability I just want it to say available or assigned to bag. I've tried to just do a general vlook-up but that doesnt work. and I've tried to separate the multi select field with a space but that didn't help either.

Any help you can provide would be greatly appreciated!

KPH

Hi @ChristineMesa

Great use case (I'm a CASA 😉).

Am I correct in thinking your request sheet looks something like this:

And you want to create something like this:

KPH

Assuming the above is correct....

This formula will return the value in the slot column* on the request sheet where one of the values in the slot column is the same as the value on the new sheet. Each value is separated by a space (CHAR(10)).

=JOIN(COLLECT({Request sheet Slot col}, {Request sheet Slot col}, HAS(@cell, Slot@row)), CHAR(10))

*you can actually use any column in place of the bold part, such as the name of the volunteer or anything else you might want to track.

Here is an example of what it would look like if we returned the Request ID instead of the slot, by referencing a different column in the Request sheet.

=JOIN(COLLECT({Request sheet Request}, {Request sheet Slot col}, HAS(@cell, Slot@row)), CHAR(10))

Now that we have that match we can use an IF to output "Available" whenever there is nothing returned or "Assigned to bag" if there is something.

=IF(ISBLANK(JOIN(COLLECT({Request sheet Slot col}, {Request sheet Slot col}, HAS(@cell, Slot@row)), CHAR(10))), "Available", "Assigned to bag")

ChristineMesa

Wow this is amazing, thank you so much, that worked! That is great you are a CASA! I've been trying to figure this out for weeks!

KPH

Glad I was able to help!