How can I write a Formula to do this.....

[alsexceladmin]

Formula teeth gnasher.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Spreadsheet			Range1
2	Ben	Site		IndividualSites	IS	JSA-6	NONE
3	IS	Crumpsall		Charlestown	18	14	14
4	JSA-6	Harpurhey		Crumpsall	18	14	14
5	NONE	Harpurhey		Harpurhey	18	14	14
6
7				Range2
8				LADVALUES	IS	JSA-6	NONE
9				Cheetham	6	2	2
10				Harpurhey	6	2	2
11				Bradford	6	2	2
12
13	Fromabovespreadsheet,howdoIcreateaformulatoproduceanumberedresultinagivencellforthefollowingexample:
14	Identifyindividualcellcontentof"A3"and"B3"(bothtext)respectively,thenmatchbothcriteriainRange"D2:G5"-
15	toproducethenumberresultofintersectingcells"E4"
16	Cellcontentin"A3","B3"comboappliedtoRange1sheetwouldgivearesultof18,similarlywith"A4","B4"combowouldgive14.
17	IalsoneedtoapplyasimilarformulatoRange2,thenaddthetworesultstogetherinagivencell.
Sheet1

 

[KenWright]

=INDEX($D$2:$G$5,MATCH(A3,$D$2:$G$2,0),MATCH(B3,$D$2:$D$5,0))

Use same syntax for second range as well

 

[alsexceladmin]

Many thanks Ken.
The Formula now returns a value, but it's the wrong one!
It returns 14 (presumably from G5) rather than 18 from E5. (any ideas?)

**I am testing this at work where I am running Excel 2000 - Windows 2000

I posted (rather poorly - sorry!) from home, where I am on Office 2003 and Xp Pro.

This won't make any difference will it?

Hope you can help

Many thanks alsexceladmin

 

[howzat]

Hi there,

Here's another approach. However, sumproduct will only work when returning numbers, such as your case:

Book1
	A	B	C	D	E	F	G
1	Spreadsheet			Range1
2	Ben	Site		IndividualSites	IS	JSA-6	NONE
3	IS	Crumpsall	18	Charlestown	18	14	14
4	JSA-6	Harpurhey	16	Crumpsall	18	14	14
5	NONE	Harpurhey	16	Harpurhey	18	14	14
6
7				Range2
8				LADVALUES	IS	JSA-6	NONE
9				Cheetham	6	2	2
10				Harpurhey	6	2	2
11				Bradford	6	2	2
Sheet2

Enter in C3 and copy down:
=SUMPRODUCT(($D$3:$D$5=B3)*($E$2:$G$2=A3)*($E$3:$G$5)+($D$9:$D$11=B3)*($E$8:$G$8=A3)*($E$9:$G$11))

 

[chiello]

Try the following:

=INDEX($E$2:$G$5,MATCH(A3,$E$2:$G$2,0),MATCH(B3,$D$3:$D$5,0))

 

[alsexceladmin]

Thanks Sean,
I am still having problems with the returns from the formula that Ken kindly provided, which appears to be working ok but....
This is still returning the wrong value, even though I have rebuilt a new sample spreadsheet and then applied the formula to it.
When I apply your formula I keep getting an error message telling me that there are not enough arguments.....(checked & double checked)

Question: Is there any configuration (or format) settings that you know of (in excel) that could be changed (by others for there own use) which would throw my formula returns out?
I can't think of any other reason for the wrong results being returned, having drafted a new example spreadsheet (which does the same)
Three of us have checked the formula entries for being copied correctly - yet it still insists on giving the result from the wrong cell.

Thanks again for your trouble

alsexceladmin (teethgnashing time!!!!)

 

[alsexceladmin]

Hi Chiello, Ken,Sean and all who are trying to help me!
Frustratingly, the formula result is still being returned from the wrong Cell(s).
The formula appears to be working ok...apart from the result returned that is!
Have rebuilt the sheet, rebuilt the formula - and had my input checked against your advised help, yet, it still throws the wrong result!!! Arrgggh!!

**I am using Office/Win 2000 at work. Does this make any difference?

We have also tried using another PC and it still gives the same result(s)

Cheers All
alsexceladmin

 

[chiello]

Ciao alsexceladmin

looking more carefully the formula, I have understood where is the problem.

Type the formula below and it will be OK

=INDEX($E$2:$G$5,MATCH(B3,$D$3:$D$5,0),MATCH(A3,$E$2:$G$2,0))

Am I right??

 

[alsexceladmin]

Magic, just the job.

Cheers Chiello (not forgetting all others)

alsexceladmin

 

[KenWright]

Dohhh - I had inadvertantly reversed the Row/Column arguments. Apologies for that