Embed MID function with Index & Match

New Member
Hello. I'm trying to use Index/Match to pull corresponding data by extracting a string from a cell, but it is not working. My extraction formula is working correctly. Formulas are below each cell. I do not want to create a new column (I do not want to eliminate the additional formula in the Employee ID column.

 Employee Total Returns Overall Score Overall Score Employee ID Tom Smith (12345) 700 87% #N/A 12345 =INDEX(C2:C10,MATCH(MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2)),A2:A10)) =MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2))

<tbody>
</tbody>

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi there, include some kind of math operation on the end of the MID function to convert the "12345" text to 12345 as a number.

=MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2)) * 1

That did not work.

=INDEX(C2:C:10,MATCH(MID(LEFT(A2,FIND(“)”,A2)-1),FIND(“(“,A2)+1,LEN(A2)*1),A2:A10))

Try

=INDEX(C2:C10,MATCH("*"&MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2))&"*",A2:A10,0))

M.

I’m now getting a #VALUE! Error

You need to be very clear if you are match text to numbers or text to text. Another source of error might be that you aren't giving a final parameter to the match function (exact match vs. match nearest value).

Also its not clear - does the lookup target cell contain "Tom Smith (12345)", which is what you posted? or Does it contain "12345", which is what you are trying to look up?

Last edited:
The first cell contains “Tom Smith (12345)”

I’m trying to use index & match to return his corresponding score, but the value I want the Match function to look up is 12345. But I need to remove the parenthesis, hence the need for the MID function.

well,

1) you are matching in A2:10, with A2 containing Tom Smith (12345).
2) You are also getting the value from A2:10, with A2 containing Tom Smith (12345).
3) You are also looking up the value from A2:10, with A2 containing Tom Smith (12345).

So it would be easier to just look up Tom Smith (12345) instead of 12345.

I'm not trying to make this unnecessariy complicated but it doesn't seem what you are trying to do goes with the data you posted or the formula you have tried starting with, so there must be something else here that you are really trying to do.

Last edited:
I have a huge data list with names and ID numbers in the format of “Smith, Tom (12345).”
I have a separate list with the same names, but in the format Tom.Smith and the employee ID in a completely separate cell. Some last names are the same so I can’t search using that as the primary key. This is why I’m trying to search for only the employee identification number.

Are you able to confirm if your list of numbers is a list of true numbers, or numbers-stored-as-text? Usually one simple test is to select 3 or 4 cells and see if the sum displayed in the status bar is summing values or not.
https://support.office.com/en-us/ar...atus-bar-f42a3ae1-3786-4294-a433-1b36a1c17ebb

This matters because if you are looking up numbers (numbers in the target lookup range) than you need to convert the values you are getting from the mid function into numbers (to get number to number comparison instead of text to number comparison). If you are looking up text, then you don't need to convert results from the mid function (text to text comparison).

Replies
16
Views
451
Replies
7
Views
238
Replies
5
Views
168
Replies
1
Views
183
Replies
7
Views
431

1,196,357
Messages
6,014,768
Members
441,847
Latest member

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.

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

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