Embed MID function with Index & Match

I

imheadsup

New Member
Joined
May 23, 2018
Messages
13
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]
 
Sort by date Sort by votes
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Employee
[/TD]
[TD]Total Returns
[/TD]
[TD]Overall Score
[/TD]
[/TR]
[TR]
[TD]Smith, Tom (12345)
[/TD]
[TD]700
[/TD]
[TD]87%
[/TD]
[/TR]
[TR]
[TD]Johnson, Mike (23456)
[/TD]
[TD]653
[/TD]
[TD]94%
[/TD]
[/TR]
[TR]
[TD]Smith, Will (45678)
[/TD]
[TD]489
[/TD]
[TD]72%
[/TD]
[/TR]
</tbody>[/TABLE]
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
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Name
[/TD]
[TD]Employee ID
[/TD]
[/TR]
[TR]
[TD]Tom.Smith
[/TD]
[TD]12345
[/TD]
[/TR]
[TR]
[TD]Mike.Johnson
[/TD]
[TD]23456
[/TD]
[/TR]
[TR]
[TD]Will.Smith
[/TD]
[TD]45678
[/TD]
[/TR]
</tbody>[/TABLE]
Sorry, but I'm not able to insert two tables into one post. Here is the second.
 
Upvote 0
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.
Click to expand...

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????
 
Last edited:
Upvote 0
Maybe something like this

Sheet1 (data)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Employee​
[/TD]
[TD]
Total Returns​
[/TD]
[TD]
Overall Score​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Smith, Tom (12345)​
[/TD]
[TD]
700​
[/TD]
[TD]
87%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Johnson, Mike (23456)​
[/TD]
[TD]
653​
[/TD]
[TD]
94%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Smith, Will (45678)​
[/TD]
[TD]
489​
[/TD]
[TD]
72%​
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Employee Name​
[/TD]
[TD]
Employee ID​
[/TD]
[TD]
Total Returns​
[/TD]
[TD]
Overall Score​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Tom.Smith​
[/TD]
[TD]
12345​
[/TD]
[TD="bgcolor: #D9D9D9"]
700​
[/TD]
[TD="bgcolor: #D9D9D9"]
0,87​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Mike.Johnson​
[/TD]
[TD]
23456​
[/TD]
[TD="bgcolor: #D9D9D9"]
653​
[/TD]
[TD="bgcolor: #D9D9D9"]
0,94​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Will.Smith​
[/TD]
[TD]
45678​
[/TD]
[TD="bgcolor: #D9D9D9"]
489​
[/TD]
[TD="bgcolor: #D9D9D9"]
0,72​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited:
Upvote 0
xenou said:
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????
Click to expand...

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.
 
Upvote 0
Marcelo Branco said:
Maybe something like this

Sheet1 (data)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1​
[/TD]
[TD]
Employee​
[/TD]
[TD]
Total Returns​
[/TD]
[TD]
Overall Score​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2​
[/TD]
[TD]
Smith, Tom (12345)​
[/TD]
[TD]
700​
[/TD]
[TD]
87%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3​
[/TD]
[TD]
Johnson, Mike (23456)​
[/TD]
[TD]
653​
[/TD]
[TD]
94%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4​
[/TD]
[TD]
Smith, Will (45678)​
[/TD]
[TD]
489​
[/TD]
[TD]
72%​
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1​
[/TD]
[TD]
Employee Name​
[/TD]
[TD]
Employee ID​
[/TD]
[TD]
Total Returns​
[/TD]
[TD]
Overall Score​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2​
[/TD]
[TD]
Tom.Smith​
[/TD]
[TD]
12345​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
700​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
0,87​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3​
[/TD]
[TD]
Mike.Johnson​
[/TD]
[TD]
23456​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
653​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
0,94​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4​
[/TD]
[TD]
Will.Smith​
[/TD]
[TD]
45678​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
489​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
0,72​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...

Finally! Worked Perfectly. Thank you. What does the "*"&$B2&"*" do?
 
Upvote 0
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)".
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
INDEX MATCH from two sheets onto third sheet?
Replies
1
Views
473
etaf
etaf
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
2K
2BLUE4U
2BLUE4U
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
556
Peter_SSs
Peter_SSs
M
  • Solved
Extracting text between multiple sets of markers and displaying on separate lines
2
Replies
12
Views
993
Fluff
Fluff
C
  • Solved
index match excel formula to vba code
Replies
9
Views
364
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top