What I’m trying to do: I need to analyze two cells that contain comma-separated text values and determine how many items appear in both cells. After getting this count, I want to display specific text based on matching criteria and some experience-related input data.
My objectives:
- Calculate how many identical terms exist between cell D3 and cell D8
- Display the content from cell D2 when specific requirements are met
I’m working with cells that have multiple values separated by commas, and I need a formula that can compare these lists and give me useful results. The tricky part is not just counting the matches, but also using that count along with other cell values to determine what gets displayed in the final output cell.
Has anyone dealt with this type of comparison in Google Sheets before? I’m looking for a formula-based solution that can handle the comma-separated format and perform the conditional logic I need.
I’ve dealt with this exact comma-separated data issue in Sheets before. For counting matches between D3 and D8, try =SUMPRODUCT(COUNTIF(SPLIT(D3,","),SPLIT(D8,"","))) - you’ll probably need to adjust it for trimming spaces though. For the conditional display, just wrap that counting formula in an IF statement that checks your match criteria and D2 content. ARRAYFORMULA helps when the comparison gets messy since SPLIT creates arrays. Heads up - extra spaces around commas will mess you up, so use TRIM inside your SPLIT functions. And throw in IFERROR if you might have empty cells, otherwise your formula will break.
I’ve dealt with this exact problem for inventory tracking. REGEX helps standardize the data first, then I use =SUMPRODUCT(MMULT(TRANSPOSE(COUNTIF(TRIM(SPLIT(D3,",")),TRIM(SPLIT(D8,",")))),SEQUENCE(COUNTA(SPLIT(D8,","))))) for matching counts. Just wrap that in your conditional logic. Pro tip: build helper columns first to test your SPLIT results before jamming everything into one monster formula. Don’t forget case sensitivity - I had to throw UPPER functions around both SPLIT operations because my data was all over the place with capitalization. The formula gets messy but it’s solid once you handle the edge cases.