index match match

[bluepenink]

Hello

Struggling to conquer this formula so can someone pls help!

=INDEX(mySource,MATCH(E$7,$S$5:$S$160,0),MATCH($C$5,$T$5:$T$160,0),MATCH($C8,$U$4:$U$160,0))

mysource = all data ($S$3:$Y$160)

E$7 = P2 on my main table
$S$5:$S$160 = range where P2, P3...P12 is stored

then check for region i.e. Northeast, Southeast
$C$5 = US Northeast
$T$5:$T$160 = range where US Northeast, US Southeast etc is stored

and then match by criteria i.e. total deals, total revenue etc

$C8 = Total deals
$U$4:$U$160 = U4, Total deals, U5 to U160 sums # of deals for different regions i.e. 160


im getting 0 as my answer. i know my logic is not straight here, so can someone kindly help, thxs!

 

[MrKowz]

If I follow your logic correctly:

=INDEX(mySource,MATCH(1,IF($S$5:$S$160=E$7,IF($T$5:$T$160=$C$5,IF($U$5:$U$160=$C8,1,""))),0))

Confirm entry with CTRL+SHIFT+ENTER, not just enter. This will store the function as an array formula. You will know it is entered properly when Excel adds {brackets} around the formula.

 

[bluepenink]

Hey buddy

thxs for the quick response. I'm getting a N/A error even after i place the array brackets.

also, changed the reference from U5 to U4, as U4 holds is the header or title that woudl match with C8

=INDEX(mySource,MATCH(1,IF($S$5:$S$160=E$8,IF($T$5:$T$160=$C$5,IF($U$4:$U$160=$C9,1,""))),0))

but getting an error. i wish i could post my workbook, dammit!

 

[MrKowz]

I'm not sure I follow that third condition correctly. Are you trying to determine what column to refer to, based on the value in C8? Can you please give a bit more detailed of an explanation (and maybe some sample data)?

 

[bluepenink]

Hello thxs again,

Basically, I have a region

US Northeast, US Southeast, US West etc,

Then im looking to get data for specific period i.e. P1, P2, P3...P12

by criteria i.e. Total deals, Total revenue.

so, for that specific region, by period and then find by criteria which is in C9

 

[MrKowz]

So Total Deals would refer to column U, Total Revenue would refer to column V?

What range does "mySource" refer to?

 

[bluepenink]

Hello

im trying to find place to post a file, but my work url is blocking alot of the sites. im not sure if u have loggin to this website, but i ahev posted the same here. thxs bud!

http://www.excelforum.com/excel-general/780208-index-match.html#post2545471

 

[MrKowz]

Use the following formula:
=INDEX(mySource,MATCH(1,IF($S$3:$S$35=E$8,IF($T$3:$T$35=$C$5,1,"")),0),MATCH($C9,$S$4:$Y$4,0))

Again, use CTRL+SHIFT+ENTER.

(Also, when you cross-post forums, aka you post same question on two forums, please provide a link to your other post(s). A lot of members are active on many excel forums, and it helps to not only keep ideas flowing, but some forums will outright lock your post if they find it on another forum)

 

[bluepenink]

hey bud

thxs alot for ur time. final q

im getting an answer, but its not for the right region.

it's showing 337 for US Northeast, when it should be 126 for P2 for US northeast.

ur thoughts? thxs again.

 

[MrKowz]

It is returning 126 for me. Did you enter that formula in cell E9 of the sample worksheet you linked to? Are you using the same formula I gave?