Help with Look Up Table Please?
 

I don't know if this is possible but here is my table:

Item Date Qty
xyz 7/3 452
xyz 7/4 225
zzy 7/2 120
abc 7/1 352

Can I do a lookup that will first search for Item, then date and return qty?
So if Item = xyz, and date = 7/4, then 452, else 0?

Any help is appreciated! TIA

Help with Look Up Table Please?
 

Say your datalist is A2 to C5,
And you enter your search criteria in
D1 = item
D2 = date

Then try this:

=SUMPRODUCT((A2:A5=D1)*(B2:B5=D2)*C2:C5)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jennifer" wrote in message
...
I don't know if this is possible but here is my table:

Item Date Qty
xyz 7/3 452
xyz 7/4 225
zzy 7/2 120
abc 7/1 352

Can I do a lookup that will first search for Item, then date and return
qty?
So if Item = xyz, and date = 7/4, then 452, else 0?

Any help is appreciated! TIA

Help with Look Up Table Please?
 

Enter as an array formula with Ctrl+Shift+Enter

E1 contains Item
E2 contains date

=IF(ISNA(INDEX($C$2:$C$5,MATCH(1,($A$2:$A$5=E1)*($ B$2:$B$5=E2),0))),0,INDEX($C$2:$C$5,MATCH(1,($A$2: $A$5=E1)*($B$2:$B$5=E2),0)))

HTH

"Jennifer" wrote:

I don't know if this is possible but here is my table:

Item Date Qty
xyz 7/3 452
xyz 7/4 225
zzy 7/2 120
abc 7/1 352

Can I do a lookup that will first search for Item, then date and return qty?
So if Item = xyz, and date = 7/4, then 452, else 0?

Any help is appreciated! TIA

Help with Look Up Table Please?
 

It worked. Thanks a bunch!

"Toppers" wrote:

Enter as an array formula with Ctrl+Shift+Enter

E1 contains Item
E2 contains date

=IF(ISNA(INDEX($C$2:$C$5,MATCH(1,($A$2:$A$5=E1)*($ B$2:$B$5=E2),0))),0,INDEX($C$2:$C$5,MATCH(1,($A$2: $A$5=E1)*($B$2:$B$5=E2),0)))

HTH

"Jennifer" wrote:

I don't know if this is possible but here is my table:

Item Date Qty
xyz 7/3 452
xyz 7/4 225
zzy 7/2 120
abc 7/1 352

Can I do a lookup that will first search for Item, then date and return qty?
So if Item = xyz, and date = 7/4, then 452, else 0?

Any help is appreciated! TIA