SOLVED

Excel issues with Wildcard when seeking countif partial match with concat data

Copper Contributor

Hi all, 

 

Thanks in advance for any help you can provide. 

 

I have data as a call matrix where I can see the volume of calls Agents are transferring to specific lines (Agents XYZ (rows), or LinesABC(cols)). 

 

I want to know if Agent A who works in Team A is transferring any calls to Lines A, B or C (could be one of multiple lines specified in external lookup table, based on the Team they are assigned to).  

To do this I broke it down into the basics of goals: GOAL 1 - is there a value in the matrix for Agent A? GOAL 2 - is the LINE where the call is transferred to linked to the Agent A based on their Team?

AgentAgent TeamLines of InterestLineALineBLineCIs There A Value?Which Line Has The Value?  Does The Line Match Any Lines of Interest
Agent ATeam ALineA, LineB, LineC1  1LineA  FALSE
Agent BTeam BLineB, , 5 1 LineB FALSE
Agent CTeam CLineB, LineC,    0   FALSE

 

So far I have Agent Name in Col A

Agent Team in Col B 

At the moment I have Xlookup of the concatenated Lines in Col C based on Data available in external Lookup Table (linked to Agent Team) - (in lookup table the Lines appear in separate cols). 

 

In Row 28 I have the Lines that Agents have transferred calls to. 

Within the matrix there is a value based on the number of calls transferred by Agent to whichever Line. 

 

So far I have: 

1) Identified if Agent A has transferred any calls to ANY line "=COUNTIF(D29:G29,">0")"

 

2) Created a very ramshackle way of identifying the name of the Line it was transferred to

"IF(D29>0,E$28,"")"

Where Row 28 refers to the name of the specific Line. This formula is repeated per column for as many cols as there are lines available within the matrix. (ideally I want to know if ANY of the cols in range (D29:G29)>0, then return the names of the specific lines in separate cols - where I can perform the lookup below on those specific).

 

3) I now want to perform a LOOKUP to see if any of THOSE cols contain the name of any Lines that Agent A is associated with, based on their Team. 

I'm basically ignoring the Lookup table and Team Name at this time, and have:

=COUNTIF(I29:K29,"*"&C29&"*")>0

Since the data in C29 (the reference of possible Lines) is concatenated ("LineA, LineB, LineC") I have used a wildcard. BUT the data in C29 is the result of another Lookup to the external Lookup Table. 

 

The result I'm getting at the moment is "FALSE" regardless of whether the result is actually TRUE or FALSE. I did at one time get a "TRUE" value, but this is when I did a hard search for the specific cell with the concat Lines within the lookup table, and I've since not been able to replicate this. 

 

4) The next step for me would THEN be to identify WHICH of those Lines where the value >0 matches the data within cell C29. 

 

Can anyone suggest 

A) a more efficient way of performing this?

B) a way to resolve the issue I'm having when searching for a partial match when comparing data within a range (I29:K29) to a concatenated list C29 (where data in C29 is the result of a lookup). 

 

I hope that this makes sense! It's so confusing to explain Excel specifics. 

3 Replies
best response confirmed by Zen_Becki (Copper Contributor)
Solution

@Zen_Becki 

It sounds like you're trying to do a partial match lookup on concatenated data that was created from an external lookup table. One issue you might be facing is that the data in C29 might contain spaces or other non-printable characters that could be throwing off your wildcard match.

To address this, you could try using the TRIM function to remove any extra spaces from the concatenated data in C29 before using the wildcard match.

For example:

=COUNTIF(I29:K29,""&TRIM(C29)&"")>0

 

Another approach you could try is to use the FILTER function to extract the columns that contain non-zero values, and then perform the partial match lookup on those columns.

For example:

=COUNTIF(FILTER(D29:G29,D29:G29>0),""&C29&"")>0

This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values. Then, it performs the partial match lookup on those columns using the COUNTIF function and the wildcard match.

 

Finally, to identify which of the lines in the extracted columns match the data in C29, you could use another FILTER function combined with the INDEX and MATCH functions.

For example:

=INDEX($E$28:$H$28,1,MATCH(TRUE,INDEX(FILTER(D29:G29,D29:G29>0),0,MATCH(""&TRIM(C29)&"",$E$28:$H$28,0))>0,0))

This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values, as before. Then, it uses the INDEX and MATCH functions to find the column in the extracted range that matches the data in C29. Finally, it uses the INDEX function again to return the name of the matching line from row 28. Note that this is an array formula, so you'll need to press Ctrl+Shift+Enter instead of just Enter to enter it in the cell.

 

The dynamic array formulas were introduced in Excel 365 and are not available in earlier versions.

It's also worth noting that some older versions of Excel may not be able to open workbooks created in later versions of Excel, especially if they use newer features or formats. So if you are working with others or sharing your workbooks, it's important to ensure compatibility across versions.

it would help a lot if you could attach a sample sheet (or post a link to it in a shared location like sharepoint or onedrive).
Assuming you have dynamic arrays (Excel 365) I think what you want might be to reverse your references in that formula:
=COUNTIF(I29:K29,"*"&C29&"*")>0
is searching the array of I29:K29 for the value of C29 but C29 is something like "LineA, LineB" so of course it won't find that text. Try reversing it:
=COUNTIF(C29,"*"&I29:K29&"*")>0
so now it will search the cell C29 multiple times for the text found in each of those cells in the array I29:K29 and the output will be something like True, True, False, so you will even know which text it found. Hope that helps.
I tried with the TRIM function and it seemed to resolve the issue. I used ChatGPT and I got there eventually! It gave me a very similar solution to above. Thank you for taking the time to answer!
1 best response

Accepted Solutions
best response confirmed by Zen_Becki (Copper Contributor)
Solution

@Zen_Becki 

It sounds like you're trying to do a partial match lookup on concatenated data that was created from an external lookup table. One issue you might be facing is that the data in C29 might contain spaces or other non-printable characters that could be throwing off your wildcard match.

To address this, you could try using the TRIM function to remove any extra spaces from the concatenated data in C29 before using the wildcard match.

For example:

=COUNTIF(I29:K29,""&TRIM(C29)&"")>0

 

Another approach you could try is to use the FILTER function to extract the columns that contain non-zero values, and then perform the partial match lookup on those columns.

For example:

=COUNTIF(FILTER(D29:G29,D29:G29>0),""&C29&"")>0

This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values. Then, it performs the partial match lookup on those columns using the COUNTIF function and the wildcard match.

 

Finally, to identify which of the lines in the extracted columns match the data in C29, you could use another FILTER function combined with the INDEX and MATCH functions.

For example:

=INDEX($E$28:$H$28,1,MATCH(TRUE,INDEX(FILTER(D29:G29,D29:G29>0),0,MATCH(""&TRIM(C29)&"",$E$28:$H$28,0))>0,0))

This formula first uses the FILTER function to extract the columns in D29:G29 that contain non-zero values, as before. Then, it uses the INDEX and MATCH functions to find the column in the extracted range that matches the data in C29. Finally, it uses the INDEX function again to return the name of the matching line from row 28. Note that this is an array formula, so you'll need to press Ctrl+Shift+Enter instead of just Enter to enter it in the cell.

 

The dynamic array formulas were introduced in Excel 365 and are not available in earlier versions.

It's also worth noting that some older versions of Excel may not be able to open workbooks created in later versions of Excel, especially if they use newer features or formats. So if you are working with others or sharing your workbooks, it's important to ensure compatibility across versions.

View solution in original post