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

limah82

New Member
Joined
Jun 20, 2019
Messages
17
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


ABCD
abc000456dedgh
#N/A​
000456Value
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Habtest

New Member
Joined
Jul 30, 2020
Messages
48
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 20, 2019
Messages
17
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

New Member
Joined
Jun 20, 2019
Messages
17
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

ABCD
abc000456dedgh#N/A000456Value
 

Habtest

New Member
Joined
Jul 30, 2020
Messages
48
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

ABCD
abc000456dedgh#N/A000456Value
Then extracting the number by mid code will do
Excel Formula:
=INDEX(D:D,MATCH(MID(A2,4,6),C:C,0))

Hope I get the question correct.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,088
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top