Help with Look Up Table Please?

[Jennifer]

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

[Ragdyer]

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" <Jennifer@discussions.microsoft.com> wrote in message
news:6FFAAD33-5A0D-4A8C-8D0D-C8B1E9B1DF12@microsoft.com...
>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

[Toppers]

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

[Jennifer]

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

[ravikiran]

How can I pick the Quantity for the nearest possible date.

For example, if the date is 7/2 and the item is xyz, then 452 should be displayed.

Thanks in advance,

Ravi.

[DonkeyOte]

@Ravi,

Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.