I am attempted to count the number of partial or complete matches between two columns in a table with some limitations:
| IA_DX | AUDIT_DX | Count |
|---|---|---|
| I21.0 | I10, I21.A1, I21.0 | 1 |
| L08.0 | L08.0 | 1 |
| D71 | D71.9 | 0 |
The limited formula I have that would give a count of 1 (for row 2 above) is:
=SUMPRODUCT(--(INDIRECT(K1985&"[IA_DX]")=INDIRECT(K1985&"[AUDIT_DX]")),--(INDIRECT(K1985&"[IA_DX]")<>""))
I want to also search for strings in IA_DX in AUDIT_DX and include matches, but not if they are not 'exact' matches like in row 3. I am also using indirects for this formula because I have a drop-down list of different sheet names. TIA!
I tried various attempts to include a wildcard and tried researching the use of SEARCH and ISNUMBER