Microsoft Excel Tutorial: Which Products Contain Both Of These Items?

Today, an odd data problem. I have a solution, but I suspect there are much easier ways to do this. I have a list of songs in workouts from Supernatural VR. Over 500 workouts are Quick Hits with just two songs. Each Quick Hits workout should be a subset of a longer workout. Given the two lists, how can you use Excel to find the parent workout?

My solution involves an XLOOKUP with a concatenated key and a concatenated lookup vector. This gives me a list of the Workout IDs that contain song 1 and the Workout ID’s that contain song 2. Given these two list of workout IDs, how can you find which workout ID is in common between the two cells?

I was surprised to find multiple matches. I called Marla Bright and we talked about Supernatural and how to narrow the choices down.

Download the data from here:

I was a guest on Marla’s For the Love of the Map podcast today. Watch that episode at https://www.youtube.com/watch?v=K1-n3yeqzqo

In today’s episode (#2661), I have a data problem, and I think there’s a more efficient way to solve it! I’ve got two lists of workout songs from a VR fitness app called Supernatural. One list is of shorter, two-song workouts called “Quick Hits,” and the other list includes the longer workouts, which can be up to twelve songs. The theory is each Quick Hit has its songs taken from one of the longer workouts. My challenge? If I give you a Quick Hit, can you help me identify which longer workout contains both songs?

To solve this, I’m using Excel’s XLOOKUP and FILTER functions to cross-check each Quick Hit with the longer workouts. I use artist and song names to match these up, and I create a unique key using the workout ID and track number to keep it all organized. But even with some clever formula tricks—like TEXTJOIN and LET—it’s getting messy. I get partial results, but some surprises are popping up. Certain song pairs are showing up in multiple workouts, which is throwing off my process.

After some trial and error (and a great conversation with Marla on the Supernatural VR podcast!), I tried adding an extra filter. Now, I’m cross-referencing to ignore any workouts with a higher ID than the Quick Hit workout ID. This helps narrow down the list, but I’m still not fully satisfied. There are cases where songs match, but they don’t necessarily match by coach or difficulty level, making it even trickier to determine the exact workout that each Quick Hit is derived from.

This project pushed my Excel formulas to the max, and I’m wondering if there’s a better approach. I’ve included the workbook in the description so you can download it and give it a try. If you have ideas on how to streamline this process or know of a simpler solution, let me know! I’d love to feature a new solution on an upcoming podcast.

Check out the Supernatural VR podcast episode featuring me as a guest, and download the workbook below. Let’s work together to solve this data mystery in Excel!

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

Table of Contents
(0:00) Looking for a better solution today
(0:15) Supernatural VR workouts and Data
(1:17) Trailer For The Love Of The Map
(2:19) Problem Description
(3:04) Finding both songs for each Quick Hit
(3:52) XLOOKUP with Concatenated Key & Concatenated Lookup Vector
(4:42) Data for other lookups with more tracks
(5:17) Matches for Song with FILTER, SORT, ARRAYTOTEXT
(6:23) Which IDs are in both lists using LET, TEXTSPLIT and XLOOKUP
(7:30) Results are finding multiple matches
(8:23) Discussion with Marla Bright
(12:43) Formula changes to throw out later workouts
(13:54) Consolidating 3 formulas into 1
(14:10) Download the data
(14:21) Join the membership
(14:40) Thanks for watching

This video answers these search terms:
Excel XLOOKUP for data matching
Excel FILTER formula tutorial
Supernatural VR workout data analysis in Excel
Quick Hits vs. long workouts data problem
Advanced Excel formula techniques for matching lists
Using TEXTJOIN and LET in Excel
Identifying duplicates in multiple Excel lists
Airtable to Excel data workflow
Data cross-referencing with Excel functions
Solving complex data problems with Excel