USE OF INDEX MATCH FOR A SPECIFIC VALUE FROM A SINGLE CELL

[azad092]

DEAR MEMBERS
GOOD MORNING
I want to use the index match formula i.e =INDEX(MINORV,MATCH(B2,MINOR,0))
In above formula MINORV is a range name and MINOR is an other range name like as column A and B
the formula is working perfect but I want to use this formula with little different , means that the Cell B2 is not contain on a single value, it may be consists on different values with comma ..... the issue is that how to use above formula for this purpose
for example if Cell B2 = MR1 then it works perfect but if Cell B2 = MR1,OT1 then #N/A error occurs
guide if this formula can be modified or an other formula can be use for this purpose

 

[arthurbr]

Try =INDEX(MINORV,MATCH("*"&B2&"*",MINOR,0))

 

[azad092]

Try =INDEX(MINORV,MATCH("*"&B2&"*",MINOR,0))

THANKS FOR YOUR KIND ATTENTION
your suggested formula return the error of #N/A

 

[arthurbr]

Perhaps Try =INDEX(MINORV,MATCH("*"&"MR1"&"*",MINOR,0))

 

[azad092]

in

Perhaps Try =INDEX(MINORV,MATCH("*"&"MR1"&"*",MINOR,0))

this way every time I have to change the formula..........means that this part "MR1"
I explain further what I want....
MINOR is name range which is consist on 100 codes i.e MR1 to MR100
and MINORV is a name of range of numbers from 1 to 100
MR1 refer to 1 and so on MR100 refer to 100
Cell B2 maybe consist on different codes i.e MR1,OT2 or MR50, OT4 or something else, the formula will only read to concerned code
if there is an other method vba or some coding please guide me