Double calculating Formulas xl2003

[How_Do_I]

In my Jeanie I’m showing two ways of getting the “Ref” number (G2 and G3) for my selected teams (E2 and F2)…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
What I was to know is… in xl 2003 is there a way to do this without the double calculating formula to eliminate the N/A?
<o> </o>
Also how could I use utilize SUMPRODUCT as opposed to CONCATENATION in my “Non Array” solution please?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Ref	Home	Away				Ref
2	1	Norwich City	Watford		Burnley	Nottingham Forest	8	Array
3	2	Bristol City	Millwall				8	Non Array
4	3	Coventry City	Portsmouth
5	4	Hull City	Swansea City
6	5	Preston North End	Doncaster Rovers
7	6	QPR	Barnsley
8	7	Reading	Scunthorpe United
9	8	Burnley	Nottingham Forest
10	9	Crystal Palace	Leicester City
11	10	Middlesbrough	Ipswich Town
12	11	Leeds Utd	Derby County
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[bolo]

try

=sumproduct(1*($B$2:$B$12=E2),1*(($C$2:$C$12=F2),$A$2:$A$12)

Regards

Bolo

 

[How_Do_I]

I've just seen that this sorts part of my question out...

=INDEX(Ref,SUMPRODUCT(MATCH(1,(Home=E2)*(Away=F2),0)))

It's the double calculation in xl 2003 that is now my concern...

 

[Aladin Akyurek]

Does Ref consist of consecutive integers, possibly starting with 1?

 

[T. Valko]

I've just seen that this sorts part of my question out...

=INDEX(Ref,SUMPRODUCT(MATCH(1,(Home=E2)*(Away=F2),0)))

It's the double calculation in xl 2003 that is now my concern...

What is the SUMPRODUCT function supposed to be doing? Is it there just so you don't need to array enter the formula?

You could do something like this that will return a 0 instead of the blank.

Array entered**:

=LOOKUP(1E100,CHOOSE({1,2},0,INDEX(Ref,MATCH(1,(Home=E2)*(Away=F2),0))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[How_Do_I]

Does Ref consist of consecutive integers, possibly starting with 1?

Hi Aladin... I bet you look at my formulas and know exactly who helped me with them...!

Yes to consecutive starting with 1... So for my Eredivisie workbook Ref will start at 1 and end at 306...

 

[How_Do_I]

Yes Biff, the SUMPRODUCT was there to eliminate the Array Formula... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Your solution is Array but eliminates the double calculation...so thanks for that!<o></o>
<o></o>
With Aladin now on the case I'm sure he'll advise on the efficiency of all these solutions...<o></o>

 

[Aladin Akyurek]

Hi Aladin... I bet you look at my formulas and know exactly who helped me with them...!

Yes to consecutive starting with 1... So for my Eredivisie workbook Ref will start at 1 and end at 306...

The following would be admissible on Excel 2003...

=LOOKUP(BigNum,CHOOSE({1,2},0,MATCH(1,IF(Home=E2,IF(Away=F2,1)),0)))

where BigNum = 9.99999999999999E+307 or just 9.99E+307.

Note. The IF form is slightly faster than binary multiplications.

 

[How_Do_I]

Thanks Aladin...

Just to make sure I did have to enter your solution with CSE... Is that right?

 

[T. Valko]

Yes Biff, the SUMPRODUCT was there to eliminate the Array Formula... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Your solution is Array but eliminates the double calculation...so thanks for that!<o></o>
<o></o>

You're welcome. Thanks for the feedback!