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]

Cool

 

[E_Tags]

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

Excel 2013/2016

	A	B	C	D
Column A	Column B	Column C	Column D
AB	Apples	12XY9009
XY	Bananas	35AB1221	Apples
DX	Pears	96AB9875	Apples
FR	Oranges	89DX6854	Pears
AA	Grapes	AAZZ123	Kiwi
ZZ	Kiwi	ZZAA123	Grapes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #FFFF00"]Bananas[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX($B$2:$B$7,MATCH(MID(C2,3,2),$A$2:$A$7,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry I missed this response. This works perfectly, thank you so much!!

 

[E_Tags]

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX($B$2:$B$7,MATCH(MID(C2,3,2),$A$2:$A$7,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This has worked perfectly for the most part, I have just encountered one issue I am hoping you can help me with? When I use the MID function to select one number it returns "#N/A". It works to return one letter, just not a number. Any ideas?

Below is the formula used:

=INDEX($AP$2:$AP$300,MATCH(MID(B4,22,1),$AN$2:$AN$300,0))

 

[FormR]

Any ideas?

Hi, if there are a combination of numbers and letters in $AN$2:$AN$300 then try to re-enter the ones in that range that are numbers with a single quote ' in front of the number to make them text.

 

[E_Tags]

Hi, if there are a combination of numbers and letters in $AN$2:$AN$300 then try to re-enter the ones in that range that are numbers with a single quote ' in front of the number to make them text.

Perfection, thank you!!