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

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
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")


M1Type1
S1Type1
Q1Type1
D1Type1
M2Type2
S2Type2
Q2Type2
D2Type2
M3Type3
S3Type3
Q3Type3
D3Type3
MType0
SType0
QType0
DType0

<tbody>
</tbody>
 
Last edited:

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
That works exactly as I wanted! Thank you sooo much :pray:

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
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,512
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top