Hello,
I would need your help with an Index, Match array formula which works partially.
In range F1:F16 I have the codes in the table below, obtained by combining 4 letters (M,S,Q,D) with nothing or 3 digits (1,2,3). These codes correspond to the types in range G1:G16.
In column A I have alphanumerical strings: what I need to do is to determine the type of those strings based on which code they contain (if any).
However, if the combination is at the beginning of the string then the result should be N/A (same if no match can be found).
For example:
String AM1H: result should be Type1
String L3J: result should be N/A (no match)
String M3H: result should be N/A (because even though there is a match this is at the beginning of my string)
The below index/match array formula works fine except when the match occurs at the beginning of the string.
The result for string M3 would hence be Type3 rather than the expected N/A.
Can anyone please suggest how the formula could be amended so that the match starts from position 2 in the string (or any other solution...)?
Thank you.
=+IFERROR(VLOOKUP(INDEX($F$1:$F$16,MATCH(1,COUNTIF(A1,"*"&$F$1:$F$16&"*"),0)),$F$1:$H$16,2,0),"N/A")
<tbody>
</tbody>
I would need your help with an Index, Match array formula which works partially.
In range F1:F16 I have the codes in the table below, obtained by combining 4 letters (M,S,Q,D) with nothing or 3 digits (1,2,3). These codes correspond to the types in range G1:G16.
In column A I have alphanumerical strings: what I need to do is to determine the type of those strings based on which code they contain (if any).
However, if the combination is at the beginning of the string then the result should be N/A (same if no match can be found).
For example:
String AM1H: result should be Type1
String L3J: result should be N/A (no match)
String M3H: result should be N/A (because even though there is a match this is at the beginning of my string)
The below index/match array formula works fine except when the match occurs at the beginning of the string.
The result for string M3 would hence be Type3 rather than the expected N/A.
Can anyone please suggest how the formula could be amended so that the match starts from position 2 in the string (or any other solution...)?
Thank you.
=+IFERROR(VLOOKUP(INDEX($F$1:$F$16,MATCH(1,COUNTIF(A1,"*"&$F$1:$F$16&"*"),0)),$F$1:$H$16,2,0),"N/A")
| M1 | Type1 |
| S1 | Type1 |
| Q1 | Type1 |
| D1 | Type1 |
| M2 | Type2 |
| S2 | Type2 |
| Q2 | Type2 |
| D2 | Type2 |
| M3 | Type3 |
| S3 | Type3 |
| Q3 | Type3 |
| D3 | Type3 |
| M | Type0 |
| S | Type0 |
| Q | Type0 |
| D | Type0 |
<tbody>
</tbody>
Last edited:
ray:
