Check Cell Content Against Range & Print Matching Results

[nparkinglot]

I've found a lot of posts that get really close to what I'm trying to do but none that seem to cover key parts that I'm trying to suss out. I'm still basically a newbie so maybe this is obvious and I'm just missing it.

I'm trying to either create a formula or a vba module that will check the contents of a cell for a partial, exact match from a range on a separate worksheet, and if it finds a match, print that match in a cell in a separate column on the same row as the original cell. Then if I found a formula I'd drag it down the column and create a macro or if it's vba I'd need it to loop back around and do the next cell down.

The reason for the 'partial, exact' is I'm working with data output from a joint outlook calendar and I have very little control over what exactly people are putting in the column in question. For example, I might have 'AD- Off-Desk' in A2, I need it to find 'AD' from the range on the other worksheet ( StaffTaskData D2:D34) and print that in a new column (G, beginning row 2). I also might have 'AAD - PTO', here I need it to find 'AAD' in the range, not 'AD'.

Any help is greatly appreciated.

 

[OaklandJim]

I can probably help. Is there always a dash after the part of the text to use for the lookup?

If possible, consider sharing relevant data use using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details. Post fake-but-realistic data for the best response.

 

[OaklandJim]

Is it always the leftmost characters that you need to "match"? Please provide some examples of how the match would work.

 

[OaklandJim]

Does this accomplish what you need?

Do Partial Match.xlsm
	A	B	C	D	E	F	G
2	AAD			AD- Off-Desk			AAD - PTO
3				BAC -
4				QAC - Back
5				ADD- Bill
6				MP- Test
7				QRT - 999
8				BBB- Desk
9				AAD - PTO
10				BQ - Up Desk
StaffTaskData

Cell Formulas
Range	Formula
G2	G2	=IFERROR( INDEX($D$2:$D$10, MATCH(A2& "*", $D$2:$D$10,0)), "not found")

In the example MATCH looks in the range of values to see if there is a "partial match." Wildcard character * means "any other characters". If there IS a "partial match" then INDEX takes the result from MATCH and "selects" the respective value in the cell where "partial match" was found. If the string sought is not found then the MATCH function returns an error, hence the IFERROR function which returns "not found" if there is no match.

 

[OaklandJim]

This example breaks the task into the two worksheets as you specified.

Do Partial Match.xlsm
	A	B	C	D	E	F	G
1	Input						Result
2	AP						not found
3	AAD						AAD - PTO
4	BB						BBB- Desk
5	QQQ						not found
6	ABC						not found
7	AD						AD- Off-Desk
8	QRT						QRT - 999
9	RRR						not found
10	MP						MP- Test
11	QAC						QAC - Back
Main

Cell Formulas
Range	Formula
G2:G11	G2	=IF(A2 = "", "", IFERROR( INDEX(StaffTaskData!$D$2:$D$10, MATCH(A2& "*", StaffTaskData!$D$2:$D$10,0)), "not found") )

Do Partial Match.xlsm
	D
1
2	AD- Off-Desk
3	BAC -
4	QAC - Back
5	ADD- Bill
6	MP- Test
7	QRT - 999
8	BBB- Desk
9	AAD - PTO
10	BQ - Up Desk
StaffTaskData

 

[nparkinglot]

This example breaks the task into the two worksheets as you specified.

Do Partial Match.xlsm
	A	B	C	D	E	F	G
1	Input						Result
2	AP						not found
3	AAD						AAD - PTO
4	BB						BBB- Desk
5	QQQ						not found
6	ABC						not found
7	AD						AD- Off-Desk
8	QRT						QRT - 999
9	RRR						not found
10	MP						MP- Test
11	QAC						QAC - Back
Main

Cell Formulas
Range	Formula
G2:G11	G2	=IF(A2 = "", "", IFERROR( INDEX(StaffTaskData!$D$2:$D$10, MATCH(A2& "*", StaffTaskData!$D$2:$D$10,0)), "not found") )

Do Partial Match.xlsm
	D
1
2	AD- Off-Desk
3	BAC -
4	QAC - Back
5	ADD- Bill
6	MP- Test
7	QRT - 999
8	BBB- Desk
9	AAD - PTO
10	BQ - Up Desk
StaffTaskData

This example breaks the task into the two worksheets as you specified.

Do Partial Match.xlsm
	A	B	C	D	E	F	G
1	Input						Result
2	AP						not found
3	AAD						AAD - PTO
4	BB						BBB- Desk
5	QQQ						not found
6	ABC						not found
7	AD						AD- Off-Desk
8	QRT						QRT - 999
9	RRR						not found
10	MP						MP- Test
11	QAC						QAC - Back
Main

Cell Formulas
Range	Formula
G2:G11	G2	=IF(A2 = "", "", IFERROR( INDEX(StaffTaskData!$D$2:$D$10, MATCH(A2& "*", StaffTaskData!$D$2:$D$10,0)), "not found") )

Do Partial Match.xlsm
	D
1
2	AD- Off-Desk
3	BAC -
4	QAC - Back
5	ADD- Bill
6	MP- Test
7	QRT - 999
8	BBB- Desk
9	AAD - PTO
10	BQ - Up Desk
StaffTaskData

I think this almost hits the nail exactly on the head, I just need it to return the partial string it was looking for, not the entire string in the cell. (Just return 'AAD', not 'AAD - PTO'. But that might actually make it simpler, right? Because I could use an IF statement and have it return the string in the target cell if true? I'm having trouble figuring out where that would actually go in this formula though.

Theoretically, yes the part of the string should always be on the left.

I really appreciate your help! This is my first time using this forum.

 

[OaklandJim]

I want to assist but I'm a bit confused. If the string searched for is AD and there is a string with ADD - Test just return the AD part or ADD? If searching for AAD and there is an entry AAD- Other just return AAD no dash. What if it finds AADD - More? Should it return "no match" if no match is found? What about AADDP - Stuff.

Hopefully you provide some more examples of the string sought and the result wanted, so I understand.

You can provide data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details. That is how I posted my attempt at a solution.

Depending on what you are looking for this may require a VBA solution. I hope that is ok.