Embed MID function with Index & Match

[imheadsup]

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.


[TABLE="width: 200"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Total Returns
[/TD]
[TD]Overall Score
[/TD]
[TD]Overall Score
[/TD]
[TD][/TD]
[TD]Employee ID
[/TD]
[/TR]
[TR]
[TD]Tom Smith (12345)
[/TD]
[TD]700
[/TD]
[TD]87%
[/TD]
[TD]#N/A
[/TD]
[TD][/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=INDEX(C2:C10,MATCH(MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2)),A2:A10))
[/TD]
[TD][/TD]
[TD]=MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ExcelElliott]

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

 

[imheadsup]

That did not work.

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

 

[Marcelo Branco]

Try

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

M.

 

[imheadsup]

I’m now getting a #VALUE! Error

 

[xenou]

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?

 

[imheadsup]

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.

 

[xenou]

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.

 

[imheadsup]

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.

 

[xenou]

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).