I am trying to find matches across two worksheets in Excel and return the value of an adjacent column. I can make this work fine using index match if I'm looking for an exact match, but I can't work out how to find a full match in one pair of columns, and a partial match in the other.
The formula I'm using is: =INDEX('Worksheet B'!$C$2:$C$318,MATCH(1,COUNTIFS('Worksheet A'$A2,'Worksheet B'!$A$2:$A$318,'Worksheet A'$F2,'Worksheet B'!$B$2:$B$318),0))
which works fine for those columns where it's searching for an exact match. But say the column I'm searching in Worksheet B contains multiple values separated by comma delimiters, and the column in Worksheet A contains a single value - how do I search for the single value from worksheet A within the multiple values on worksheet B?
I've tried using "*&'Worksheet A'!A2&"*" but no joy. Am I barking up the wrong tree with index/match?
Here's the column with multiple values I'm trying to match. Apologies that I can't post more of the spreadsheet but it contains sensitive data. snip of spreadsheet column with multiple values
Many many thanks for any help and let me know if you need more info.