VLOOKUP for cells that contain the search value but may not have an exact match?

Laura Beth

I would like to use VLOOKUP to automatically fill cells with information from one sheet to another. On one sheet, each row of data represents one event. The other sheet (referenced by VLOOKUP) is for reserving resources for the event.

Sheet A:

Sheet B (referenced by VLOOKUP):

The issue I'm having is that sometimes multiple events will use the same resources on the same day, meaning in Sheet B we have multiple "program codes" listed for one row of data. Program codes are never repeated - one program code will only ever be associated with one row of data on Sheet B.

My issue is that VLOOKUP doesn't actually work with my setup because that function looks for exact matches instead of looking for cells that contain the search value. I was trying to think of ways to combine the CONTAINS, IF, and VLOOKUP functions, but I can't figure out how to do that and actually get what I need.

Does anyone have suggestions? Thanks!

Paul Newcome

Try an INDEX/COLLECT...

=INDEX(COLLECT({Range to Pull}, {Program Codes Column}, CONTAINS([Program Code]@row, @cell)), 1)

Paul Newcome

Try an INDEX/COLLECT...

=INDEX(COLLECT({Range to Pull}, {Program Codes Column}, CONTAINS([Program Code]@row, @cell)), 1)

Laura Beth

@Paul Newcome thank you so much! This worked perfectly!

Paul Newcome

Happy to help. 👍️