how to nest a mid function within the array of index match ?

[limah82]

Dear All,

first of all, thanks in advance for your help.

I am trying to combine a mid function with a index match in order to return a value if the n-th characters of a string is equal to C2 (in this case =MID(A2,4,6)

=INDEX(D:D,MATCH(C2,A:A,0))

Is it possible to implement mid formula in an array?

I have also tried to concatenate an wildcard (*) to the c2 value but it did not work neither

A	B	C	D
abc000456dedgh	#N/A​	000456	Value

 

[Habtest]

Concatenating astroids before and after the target string works in Office365
=INDEX(D:D,MATCH("*"&C2&"*",A:A,0))
or
=INDEX(D:D,MATCH("???"&C2&"?????",A:A,0))

If not, please specify your office version

 

[limah82]

I might have missed the "quotes" when I tried... Thanks a mil the formula works perfectly... and using the "?" allows me to determine exacly in which position the value I am looking for is in the string

 

[limah82]

Hello everybody, I just realized that my initial formula was not written in the proper way, so the solution does not tackle the problem

the formula I want to correct is:

=INDEX(D:D,MATCH(A2,C:C,0))

I want to find into C:C part of the content displayed in A2

A	B	C	D
abc000456dedgh	#N/A	000456	Value

 

[Habtest]

Hello everybody, I just realized that my initial formula was not written in the proper way, so the solution does not tackle the problem

the formula I want to correct is:

=INDEX(D:D,MATCH(A2,C:C,0))

I want to find into C:C part of the content displayed in A2

A	B	C	D
abc000456dedgh	#N/A	000456	Value

Then extracting the number by mid code will do

=INDEX(D:D,MATCH(MID(A2,4,6),C:C,0))

Hope I get the question correct.