Index Match with Wildcards across multiple sheets

[scorziello]

I am trying to use index match with a wildcard option between 2 sheets. I have this formula that works great if all the data is on one sheet.

=INDEX(C:C,MATCH("*"&F:F&"*",B:B,0))

I have attached a mini sheet of sheet 2 where you can see that when I use the same formula but with a wildcard I get an error. Sheet 3 shows the formula working.

sample.xlsx
	A	B
1	login	cohort
2	loricew	as233
3	dolmosa	af515
4	vjesseho	ae513
5	fishtyl	fe381
6	fishtyl	ge265
Sheet1

sample.xlsx
	A	B	C
1	Cohort		title
2	#N/A		GRR1 DL Productivity Retrain Request loricew
3	#N/A		GRR1 NA Quality Retrain Request - dolmosa
4	#N/A		GRR1 NA Quality Retrain Request - vjesseho
5	#N/A		GRR1 NA Quality Retrain Request - frankjna
6	#N/A		GRR1 RT Quality Retrain Request - fishtyl
Sheet2

Cell Formulas
Range	Formula
A2	A2	=INDEX(Sheet1!B:B,MATCH("*"&C:C&"*",Sheet1!A:A,0))
A3:A6	A3	=INDEX(Sheet1!D:D,MATCH("*"&C:C&"*",Sheet1!A:A,0))

sample.xlsx
	A	B	C	D	E	F	G
1		GRR1 DL Productivity Retrain Request loricew	as233	dolmosa			af515
2		GRR1 NA Quality Retrain Request - dolmosa	af515	frankjna			fe381
3		GRR1 NA Quality Retrain Request - vjesseho	ae513	vjesseho			ae513
4		GRR1 NA Quality Retrain Request - frankjna	fe381	fishtyl			ge265
5		GRR1 RT Quality Retrain Request - fishtyl	ge265	loricew			as233
6
Sheet3

Cell Formulas
Range	Formula
G1:G5	G1	=INDEX(C:C,MATCH("*"&D:D&"*",B:B,0))

 

[jasonb75]

You're getting an error because there are no matching entries in sheet 1.

edit:- I see what you're trying to do. There is a significant difference between the single sheet example and the 2 sheet example in that you have switched the long and short string columns around. For that, wildcards are the wrong way to to it. Theoretically you would need MATCH(C:C,"*"&Sheet1!A:A&"*",0) which is not valid syntax so an alternative method will be needed.

=INDEX(Sheet1!B:B,MATCH(TRIM(MID(C2,FIND("-",C2)+1,LEN(C2))),Sheet1!A:A,0))

 

[scorziello]

You're getting an error because there are no matching entries in sheet 1.

edit:- I see what you're trying to do. There is a significant difference between the single sheet example and the 2 sheet example in that you have switched the long and short string columns around. For that, wildcards are the wrong way to to it. Theoretically you would need MATCH(C:C,"*"&Sheet1!A:A&"*",0) which is not valid syntax so an alternative method will be needed.

=INDEX(Sheet1!B:B,MATCH(TRIM(MID(C2,FIND("-",C2)+1,LEN(C2))),Sheet1!A:A,0))

That works great!! Thank you so much. Now I just need to remove some unnecessary characters from some of the entries and it will work on my actual sheet. Sheet 2 Column C are subject lines from our support tickets and some people like to add brackets around the logins but I will tackle that tomorrow when I get back to the office.

 

[jasonb75]

some people like to add brackets around the logins

That's easy enough, assuming standard brackets ( and )

=INDEX(Sheet1!B:B,MATCH(TRIM(SUBSTITUTE(SUBSTITUTE(MID(C2,FIND("-",C2)+1,LEN(C2)),"(",""),")","")),Sheet1!A:A,0))

Or educate people to do it properly, although that is often easier said than done. Could try telling them that adding brackets to the ID invalidates it for data analysis and makes in look like they are not performing adequately

 

[scorziello]

That is brilliant!! I will test this out when I get back to the office. Thank you.

 

[scorziello]

That did the trick!! Thank you so much for your help. I think I may try and make this into a macro to account for other variables but this gets me going in the right direction.