Forum Discussion
Excel issues with Wildcard when seeking countif partial match with concat data
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 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.
- 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!
