2 + 1 dimentional search

**jacknjob** · 05-12-2007, 02:42 AM

If I have a 2 + 1 dimentional table to search, how do I use the Excel formula for it?

Table

Name Age Jan Feb Mar Apr May Jun
John 10 100 200 300 400 500 600
Jack 20 100 200 300 400 500 600
Jake 30 100 200 300 400 500 600
Jane 40 100 200 300 400 500 600
June 50 100 200 300 400 500 600

Results

Seach for Jane and 50 and Apr = 500.

I need the formula, index does not work well, sumproduct can't work on the row search...

**mudraker** · 05-12-2007, 02:58 AM

Do you mean Jane and 50 or should it be June and 50?

**jacknjob** · 05-12-2007, 08:51 AM

yah it's Jane 40.

**jacknjob** · 05-13-2007, 09:51 PM

Is there anyone who can help with this?

**duane** · 05-13-2007, 09:59 PM

what exactly do you want to do? Find the total for June when jane and 40 are in columns a and b?

**jacknjob** · 05-13-2007, 11:16 PM

I need to find the formula in the Result page that return a value from 2 columns + 1 row search.

**duane** · 05-13-2007, 11:31 PM

this is an array formaula so enter with control+shift+enter

=OFFSET(Database!$A$1,MATCH(1,($C$1=Database!$A$2:$A$42)*($C$2=Database!$B2:$B42),0),MATCH($C$3,Database!$B$1:$N$1,0))

**duane** · 05-13-2007, 11:47 PM

actually Bryan's solution should work as well

=sumproduct(($c$1=database!$a$2:$a$42)*($c$2=database!$b$2:$b$42)*($c$3=database!$c$1:$n$1)*(database!$c$2:$n$42))

this adds everyting in c2:n42 for which the critera for columns a and b are met, as well as the criteria for the column heading - which should be a unique solution, but if your data showed 2 rows having the criteria for example, the results would add