# Index, Match array formula with match starting from 02nd position?

#### Bering

##### Board Regular
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")

 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:

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

##### MrExcel MVP, Moderator
Try

=IFERROR(VLOOKUP(INDEX(\$F\$1:\$F\$16,MATCH(1,COUNTIF(A1,"?"&\$F\$1:\$F\$16&"*"),0)),\$F\$1:\$H\$16,2,0),"N/A")

#### Bering

##### Board Regular
That works exactly as I wanted! Thank you sooo much ray:

Try

=IFERROR(VLOOKUP(INDEX(\$F\$1:\$F\$16,MATCH(1,COUNTIF(A1,"?"&\$F\$1:\$F\$16&"*"),0)),\$F\$1:\$H\$16,2,0),"N/A")

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback

Replies
3
Views
46
Replies
10
Views
113
Replies
2
Views
87
Replies
3
Views
77
Replies
18
Views
128