vlookup or match index with two criteria help

[gondal]

dear all
PFA..i want to place package details in sheet 1 from column "T to AH" with the ref of "packages sheet" details through "cust"...

there is two 500 in sheet 1 from "T to AH" pl ignore first one..

Gondal

[The_Snook]

Hi gondal,

If I'm understanding your query correctly placing the following formula in cell T2 and copying it across and down will do what you need.

=SUMIFS(PACKAGES!$F:$F,PACKAGES!$E:$E,Sheet1!T$1,PACKAGES!$A:$A,Sheet1!$E2)

Regards,

Snook

[CAntosh]

I went this route:

=SUMPRODUCT((PACKAGES!$A$2:$A$447=Sheet1!$E17)*(PACKAGES!$E$2:$E$447=Sheet1!AD$1),PACKAGES!$F$2:$F$447)

Same idea: Place the formula in T2 with Ctrl + Shift + Enter, then fill down and across. If you want to clear one of the "500" columns, just delete the entries afterward.

EDIT: ERROR! This is the formula from a random middle cell (AD17), see post #6 for the proper formula!!

[gondal]

thnx snook its working as i need..thnx

[gondal]

sorry cantosh bro..your formula is not given me desired result...

[CAntosh]

Sorry! I posted the formula from a random middle square. The formula should have been this, posted in T2 as an array formula:

=SUMPRODUCT((PACKAGES!$A$2:$A$447=Sheet1!$E2)*(PACKAGES!$E$2:$E$447=Sheet1!T$1),PACKAGES!$F$2:$F$447)

But if Snook's works for you, too, go with what works best for you!

[gondal]

great cantosh bro..its nicely working too..now i have two different formulas for my future use