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

alsexceladmin

Board Regular
Joined
Jan 17, 2005
Messages
62
Formula teeth gnasher.xls
ABCDEFGHIJKLM
1SpreadsheetRange1
2BenSiteIndividualSitesISJSA-6NONE
3ISCrumpsallCharlestown181414
4JSA-6HarpurheyCrumpsall181414
5NONEHarpurheyHarpurhey181414
6
7Range2
8LADVALUESISJSA-6NONE
9Cheetham622
10Harpurhey622
11Bradford622
12
13Fromabovespreadsheet,howdoIcreateaformulatoproduceanumberedresultinagivencellforthefollowingexample:
14Identifyindividualcellcontentof"A3"and"B3"(bothtext)respectively,thenmatchbothcriteriainRange"D2:G5"-
15toproducethenumberresultofintersectingcells"E4"
16Cellcontentin"A3","B3"comboappliedtoRange1sheetwouldgivearesultof18,similarlywith"A4","B4"combowouldgive14.
17IalsoneedtoapplyasimilarformulatoRange2,thenaddthetworesultstogetherinagivencell.
Sheet1
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
=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

Board Regular
Joined
Jan 17, 2005
Messages
62
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

Board Regular
Joined
Oct 13, 2003
Messages
144
Hi there,

Here's another approach. However, sumproduct will only work when returning numbers, such as your case:
Book1
ABCDEFG
1SpreadsheetRange1
2BenSiteIndividualSitesISJSA-6NONE
3ISCrumpsall18Charlestown181414
4JSA-6Harpurhey16Crumpsall181414
5NONEHarpurhey16Harpurhey181414
6
7Range2
8LADVALUESISJSA-6NONE
9Cheetham622
10Harpurhey622
11Bradford622
Sheet2


Code:
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

Well-known Member
Joined
Jan 18, 2005
Messages
848

ADVERTISEMENT

Try the following:

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

:)
 

alsexceladmin

Board Regular
Joined
Jan 17, 2005
Messages
62
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

Board Regular
Joined
Jan 17, 2005
Messages
62

ADVERTISEMENT

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 :oops: :rolleyes: :oops:
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Ciao alsexceladmin

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

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??

:LOL: :LOL:
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Dohhh - I had inadvertantly reversed the Row/Column arguments. Apologies for that :(
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top