Index and Concatenate

[How_Do_I]

Using A1&B1… how would I use INDEX to return 4 in C4 please?

Excel Workbook
	A	B	C	D	E	F	G
1	Fiorentina	Napoli	4		10	AS Roma	Cesena
2					23	Udinese	Genoa
3					54	Bari	Juventus
4					12	Chievo Verona	Catania
5					14	AC Milan	Lecce
6					15	Parma	Brescia
7					9	Sampdoria	Lazio Roma
8					104	Palermo	Cagliari
9					4	Fiorentina	Napoli
10					7	Bologna	Inter Milan
11					45	Inter Milan	Udinese
12					3	Genoa	Chievo Verona
13					2	Juventus	Sampdoria
Sheet1

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

 

[schielrn]

H1, just enter and copy down:

=F1&"|"&G1

Now you can invoke in C1:

=INDEX($A$1:$E$13,MATCH(A1&"|"&B1,$H$1:$H$13,0))

which needs just enter.

May I ask, is there a special reason you are inserting the | character? Is it just to make sure things remain unique?

 

[MrKowz]

May I ask, is there a special reason you are inserting the | character? Is it just to make sure things remain unique?

That would precicely be the reason. Say you have the following data and you need to concatenate the two columns in order to do a lookup.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">111</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

If you straight up did a concatenation, each would would return 1111:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">111</td><td style="text-align: right;;">1111</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=A1&B1</td></tr></tbody></table></td></tr></table><br />

However, to keep them unique, you can split the values with any special character:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style=";">11|11</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">111</td><td style="text-align: right;;">1</td><td style=";">111|1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">111</td><td style=";">1|111</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=A1&"|"&B1</td></tr></tbody></table></td></tr></table><br />

 

[Aladin Akyurek]

May I ask, is there a special reason you are inserting the | character? Is it just to make sure things remain unique?

Never take the risk, even if slim...

A1: 1
B1: 12
A2: 11
B2: 2

=A1&B1 ==> 112
=A2&B2 ==> 112

=A1&"|"&B1 ==> 1|12
=A2&"|"&B2 ==> 11|2

 

[How_Do_I]

Beautiful, thanks Aladin

 

[How_Do_I]

May I ask, is there a special reason you are inserting the | character?

How did you know my next question!!!... Brilliant, thanks everyone...

 

[schielrn]

Never take the risk, even if slim...

A1: 1
B1: 12
A2: 11
B2: 2

=A1&B1 ==> 112
=A2&B2 ==> 112

=A1&"|"&B1 ==> 1|12
=A2&"|"&B2 ==> 11|2

That is what I suspected, thank you very much for the insight.

 

[How_Do_I]

Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
What about this scenario…is there a way to get the 4 in E1 without using an array formula please?

Excel Workbook
	A	B	C	D	E
1	Lokomotiv Moscow	Dinamo Moscow		Terek Grozny|Zenit St Petersburg	4
2	Spartak Nalchik	PFK Samara Kryliya
3	Anzhi Makhachkala	Krasnodar
4	Terek Grozny	Zenit St Petersburg
5	Kuban Krasnodar	FK Rubin Kazan
6	CSKA Moscow	FK Amkar Perm'
7	Volga	FK Tom' Tomsk
8	FK Rostov-Na-Donu	Spartak Moscow
Sheet1

 

[Haseeb Avarakkan]

Hello, try

=MATCH(D1,INDEX(A1:A8&"|"&B1:B8,),0)

This is still an array formula, but it would avoid three key combination Ctrl+Shift+Enter. Just Enter is enough.

 

[How_Do_I]

Hello, try

=MATCH(D1,INDEX(A1:A8&"|"&B1:B8,),0)

This is still an array formula, but it would avoid three key combination Ctrl+Shift+Enter. Just Enter is enough.

Thanks a lot for that... it works, so I'm happy...