Combining INDEX/MATCH with LEFT

[tradeaccepted]

Hello,

I am trying to use INDEX/MATCH with LEFT, to change the returned value.

Example:

[TABLE="width: 300"]
<tbody>[TR]
[TD]Data1[/TD]
[TD]Data2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apples {are great}<are good=""></are>[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Oranges {are also great}<are also="" good=""></are>[/TD]
[/TR]
</tbody>[/TABLE]


=INDEX(B:B,MATCH(5,A:A,0))

This Formula returns the value of Apples {are great}.
Lets say the INDEX/MATCH formula above is in cell C2. I can run the following formula on cell C2 to return only Apples. =LEFT(H2,FIND("{",C2)-1).


How can I combine these two formulas, so that I have only one formula that returns the value of only "Apples"?

 

[Eric W]

I looked at your previous post. From context I deduced that you must have used < and > to delineate the substrings. As you found, the forum software here interprets those as HTML tags, and tends to mess things up. You can put a space before and after them if you really need to use them. But using {} works too.

	A	B	C	D	E	F	G
Data1	Data2
Apples {are great}	Apples {are great}	Apples	Apples	Apples
Oranges {are also great}	Oranges {are also great}	Oranges	Oranges	Oranges
Grapes	Grapes	Grapes

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]

</tbody>

Sheet13​

[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] "]E2[/TH]
[TD="align: left"]=LEFT(C2,FIND("{",C2)-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=LEFT(INDEX(B:B,MATCH(A2,A:A,0)),FIND("{",INDEX(B:B,MATCH(A2,A:A,0)))-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(INDEX(B:B,MATCH(A2,A:A,0)),"{",REPT(" ",99)),99))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=INDEX(B:B,MATCH(A2,A:A,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



In this example, column C and E are your two formulas. Column F is how to combine them directly. But since that version can cause errors (row 4) if the symbol isn't found, you might want to try the formula in G.

Hope this helps.

 

[tradeaccepted]

Eric,

Thank you very much for taking the time to do this. This is a great example, and thanks again for taking the time to point out the potential errors. You are a champion.