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]

Try:

=index($e$1:$e$13,match(1,($f$1:$f$13=a1)*($g$1:$g$13=b1),0))

This needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

 

[Aladin Akyurek]

Some options...

1) Control+shift+enter, not just enter:

=INDEX($E$1:$E$13,MATCH(1,IF($F$1:$F$13=A1,IF($G$1:$G$13=B1,1)),0))

2) Control+shift+enter, not just enter

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

3) Finally, also control+shift+enter...

=VLOOKUP(A1&"|"&B1,CHOOSE({1,2},$F$1:$F$13&"|"&$G$1:$G$13,$E$1:$E$13),2,0)

Option 1 would be less costly...

 

[How_Do_I]

=index($e$1:$e$13,match(1,($f$1:$f$13=a1)*($g$1:$g$13=b1),0))

That works thanks,... is there any way to do it without the CSE though please?

 

[How_Do_I]

Some options...

1) Control+shift+enter, not just enter:

=INDEX($E$1:$E$13,MATCH(1,IF($F$1:$F$13=A1,IF($G$1:$G$13=B1,1)),0))

2) Control+shift+enter, not just enter

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

3) Finally, also control+shift+enter...

=VLOOKUP(A1&"|"&B1,CHOOSE({1,2},$F$1:$F$13&"|"&$G$1:$G$13,$E$1:$E$13),2,0)





Option 1 would be less costly...

Thanks Aladin...

 

[schielrn]

That works thanks,... is there any way to do it without the CSE though please?

You could do it using sumproduct, but this will ONLY work if you do NOT have duplicate values as it would basically sum them together if there were duplicate:

=sumproduct($e$1:$e$13,--($f$1:$f$13=a1),--($g$1:$g$13=b1))

Hope that helps.

 

[MrKowz]

That works thanks,... is there any way to do it without the CSE though please?

You could use SUMPRODUCT, but SUMPRODUCT evaluates as an array anyway, so you wouldn't really be saving any processing time.

 

[Aladin Akyurek]

Thanks Aladin...

You are welcome. If you want to, you can concatenate the data and invoke a regular Index/Match formula...

 

[How_Do_I]

You are welcome. If you want to, you can concatenate the data and invoke a regular Index/Match formula...

I tried that Aladin but I couldn't get it to work... Would you point me in the right direction please?

 

[Aladin Akyurek]

I tried that Aladin but I couldn't get it to work... Would you point me in the right direction please?

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.