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.

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>

 

[imheadsup]

A	B	C
Employee	Total Returns	Overall Score
Smith, Tom (12345)	700	87%
Johnson, Mike (23456)	653	94%
Smith, Will (45678)	489	72%

<tbody>
</tbody>

The Format of my data is "general" as it contains text and numbers. I have two separate worksheets that I'm trying to use to pull this data. So as you see, I'm trying to extract the employee ID number from between the parenthesis on the first sheet and find the corresponding value for each respective employee. I'm not necessarily trying to return a numerical value, just the value that the MATCH function should be looking for.


The second Sheet looks like:

A B
Employee Employee ID
Tom.Smith 12345
Mike.Johnson 23456
Will.Smith 45678

 

[imheadsup]

Employee Name	Employee ID
Tom.Smith	12345
Mike.Johnson	23456
Will.Smith	45678

<tbody>
</tbody>

Sorry, but I'm not able to insert two tables into one post. Here is the second.

 

[xenou]

Employee Name Employee ID
Tom.Smith 12345
Mike.Johnson 23456
Will.Smith 45678
Sorry, but I'm not able to insert two tables into one post. Here is the second.

We need to know the DATATYPE of the second column in this worksheet (not the format). Are the IDs true numbers, or are they numbers stored as text?

Also, What are you trying to lookup? The second data table has no information in it that is not already in the first table????

 

[Marcelo Branco]

Maybe something like this

Sheet1 (data)

	A​	B​	C​
1​	Employee​	Total Returns​	Overall Score​
2​	Smith, Tom (12345)​	700​	87%​
3​	Johnson, Mike (23456)​	653​	94%​
4​	Smith, Will (45678)​	489​	72%​

<tbody>
</tbody>

Sheet2

	A​	B​	C​	D​
1​	Employee Name​	Employee ID​	Total Returns​	Overall Score​
2​	Tom.Smith​	12345​	700​	0,87​
3​	Mike.Johnson​	23456​	653​	0,94​
4​	Will.Smith​	45678​	489​	0,72​

<tbody>
</tbody>

Formula in C2 copied across and down (gray area)
=INDEX(Sheet1!B:B,MATCH("*"&$B2&"*",Sheet1!$A:$A,0))

If required you can format column D as percent.

Hope this helps

M.

 

[imheadsup]

We need to know the DATATYPE of the second column in this worksheet (not the format). Are the IDs true numbers, or are they numbers stored as text?

Also, What are you trying to lookup? The second data table has no information in it that is not already in the first table????

The first table has the employee's score. The second table is a database of all employee information. I want that employee's score to be returned based off of that employee's Employee ID Number, which is in parenthesis. Unless you have another suggestion as to how I can search for that employee based off of the info given in the second table.

 

[imheadsup]

Maybe something like this

Sheet1 (data)

	A​	B​	C​
1​	Employee​	Total Returns​	Overall Score​
2​	Smith, Tom (12345)​	700​	87%​
3​	Johnson, Mike (23456)​	653​	94%​
4​	Smith, Will (45678)​	489​	72%​

<tbody>
</tbody>

Sheet2

	A​	B​	C​	D​
1​	Employee Name​	Employee ID​	Total Returns​	Overall Score​
2​	Tom.Smith​	12345​	700​	0,87​
3​	Mike.Johnson​	23456​	653​	0,94​
4​	Will.Smith​	45678​	489​	0,72​

<tbody>
</tbody>

Formula in C2 copied across and down (gray area)
=INDEX(Sheet1!B:B,MATCH("*"&$B2&"*",Sheet1!$A:$A,0))

If required you can format column D as percent.

Hope this helps

M.

Finally! Worked Perfectly. Thank you. What does the "*"&$B2&"*" do?

 

[xenou]

It matches anything "like" 12345 (in the first case, or anything like the ID in the general case).

for instance:
abc12345def
abc12345
12345def

Therefore, given the value "12345" you can match "Tom Smith (12345)".