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

##### Board Regular
Formula teeth gnasher.xls
ABCDEFGHIJKLM
2BenSiteIndividualSitesISJSA-6NONE
3ISCrumpsallCharlestown181414
4JSA-6HarpurheyCrumpsall181414
5NONEHarpurheyHarpurhey181414
6
7Range2
9Cheetham622
10Harpurhey622
12
14Identifyindividualcellcontentof"A3"and"B3"(bothtext)respectively,thenmatchbothcriteriainRange"D2:G5"-
15toproducethenumberresultofintersectingcells"E4"
16Cellcontentin"A3","B3"comboappliedtoRange1sheetwouldgivearesultof18,similarlywith"A4","B4"combowouldgive14.
Sheet1

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### KenWright

##### Active Member
=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

##### Board Regular
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

#### howzat

##### Board Regular
Hi there,

Here's another approach. However, sumproduct will only work when returning numbers, such as your case:
Book1
ABCDEFG
2BenSiteIndividualSitesISJSA-6NONE
3ISCrumpsall18Charlestown181414
4JSA-6Harpurhey16Crumpsall181414
5NONEHarpurhey16Harpurhey181414
6
7Range2
9Cheetham622
10Harpurhey622
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
Try the following:

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

##### Board Regular
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.

##### Board Regular
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

#### chiello

##### Well-known Member

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

##### Board Regular
Magic, just the job.

Cheers Chiello (not forgetting all others)

#### KenWright

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

Replies
6
Views
199
Replies
12
Views
535
Replies
11
Views
780
Replies
8
Views
743
Replies
3
Views
443

1,181,127
Messages
5,928,256
Members
436,594
Latest member
asifm0

### 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.

### Which adblocker are you using?

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

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