If a cell contains specific characters in specific positions, return another specified value

[E_Tags]

Can someone please help me with a formula to do the following? I can't find any previous solutions.

If a value from column A, features in any value in column C as the 3rd and 4th value, return the value from column B corresponding to the value in column A. Results being returned in column D.




[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]Apples[/TD]
[TD]12XY9009[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XY[/TD]
[TD]Bananas[/TD]
[TD]35AB1221[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DX[/TD]
[TD]Pears[/TD]
[TD]96AB9875[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD]Oranges[/TD]
[TD]89DX6854[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the board

If I have understood correctly here is one option you can try, note - this isn't as robust as checking the 3rd and 4th characters specifically but maybe it is robust enough?

Excel 2013/2016
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	AB	Apples	12XY9009	Bananas
3	XY	Bananas	35AB1221	Apples
4	DX	Pears	96AB9875	Oranges
5	FR	Oranges	89DX6854	#N/A
Sheet2

Cell Formulas
Range	Formula
D2	=INDEX($B$2:$B$5,MATCH("*"&A2&"*",$C$2:$C$5,0))

 

[E_Tags]

@MrExcel MVP, thank you for your response however I need it to only return when the values feature in that exact location as there will be the instance when those values could appear in column C in different positions but they are not relevant.


What does "*"&A2&"*" do?

 

[FormR]

I need it to only return when the values feature in that exact location as there will be the instance when those values could appear in column C in different positions but they are not relevant.

Excel 2013/2016
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	AB	Apples	12XY9009	Pears
3	XY	Bananas	35AB1221	Apples
4	DX	Pears	96AB9875	Oranges
5	FR	Oranges	89DX6854	#N/A
6	AA	Grapes	AAZZ123	Kiwi
7	ZZ	Kiwi	ZZAA123	Grapes
Sheet2

Cell Formulas
Range	Formula
D2	=LOOKUP(2,1/(MID($C$2:$C$7,3,2)=A2),$B$2:$B$7)

What does "*"&A2&"*" do?

The * characters are wild cards - so we are saying match anything & A2 & anything

 

[Rick Rothstein]

Excel 2013/2016
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	AB	Apples	12XY9009	Pears
3	XY	Bananas	35AB1221	Apples
4	DX	Pears	96AB9875	Oranges
5	FR	Oranges	89DX6854	#N/A
6	AA	Grapes	AAZZ123	Kiwi
7	ZZ	Kiwi	ZZAA123	Grapes
Sheet2

Cell Formulas
Range	Formula
D2	=LOOKUP(2,1/(MID($C$2:$C$7,3,2)=A2),$B$2:$B$7)

The * characters are wild cards - so we are saying match anything & A2 & anything

You could have stayed with your original formula by just changing the first asterisk to two question marks...

=INDEX($B$2:$B$5,MATCH("??"&A2&"*",$C$2:$C$5,0))

Whereas an asterisk wildcard stands in for zero or more characters, a question mark wildcard stands in for exactly one character.

 

[FormR]

You could have stayed with your original formula by just changing the first asterisk to two question marks...

=INDEX($B$2:$B$5,MATCH("??"&A2&"*",$C$2:$C$5,0))

Doh - thanks Rick.

 

[E_Tags]

Thank you for your help on this, very much appreciated. This is definitely closer However why does D5 not return Pears?

I put the following formula into my actual sheet in the same format and it is returning #N/A for all in column D --> =INDEX($B$2:$B$105,MATCH(TRUE,INDEX(MID($C$2:$C$5,3,2)=A2,0),0))

My actual column C values are 25 characters and appear like so: FCCOSB-10000600-CR00MCW52

 

[FormR]

However why does D5 not return Pears?

I think we (I) had the lookup the wrong way round, you can try..

Excel 2013/2016
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	AB	Apples	12XY9009	Bananas
3	XY	Bananas	35AB1221	Apples
4	DX	Pears	96AB9875	Apples
5	FR	Oranges	89DX6854	Pears
6	AA	Grapes	AAZZ123	Kiwi
7	ZZ	Kiwi	ZZAA123	Grapes
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX($B$2:$B$7,MATCH(MID(C2,3,2),$A$2:$A$7,0))

 

[E_Tags]

I think I've created some confusion, let me explain what I want the results to communicate:

The values in Column C need to be coded based on what values appear as the 3rd and 4th characters. AA as the 3rd and 4th characters means apples, AB means Apples bananas, AC Apples Cake, etc.

If AB appears as the 3rd and 4th value in any value of Column C, it means that that row needs to be coded as Apples Bananas in Column D, and so on...


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]Apples[/TD]
[TD]FCABTG-09876543-75HY94HT7[/TD]
[TD]Apples Bananas[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]Apples Bananas[/TD]
[TD]FCASHT-67847565-748FHT867[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]Apples Cake[/TD]
[TD]FCAARG-72839465-846THFGR7[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Apples Dessert[/TD]
[TD]ADBAYH-93647586-93JFT5867[/TD]
[TD]Banana Assort[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]Banana Assort[/TD]
[TD]BABCWD-93748590-KFHG86756[/TD]
[TD]Banana Cake[/TD]
[/TR]
[TR]
[TD]BC[/TD]
[TD]Banana Cake[/TD]
[TD]GTABYH-01029384-93657FHT8[/TD]
[TD]Apples Bananas[/TD]
[/TR]
[TR]
[TD]BF[/TD]
[TD]Banana Fruit[/TD]
[TD]YHADUY-08675465-94HDTRG67[/TD]
[TD]Apples Dessert[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas? Apologies, my initial explanation allowed room for confusion.

 

[E_Tags]

Mr Excel MVP sorry I missed this response. This works perfectly, thank you so much!!