Lookup and mach

I have 2 worksheets Data and Invent_Count.

In transit invent I have in A a list of all invertory codes and in row 1
order nr not shipped
Worksheet data is in database format with a column for Order_NR an a colmn
for Invent_Code

In Invent_Count!c2 I want to place a formula that would look in Data!A2:C500
match Invent_Count!A2 & Invent_Count!C1 and give me the quantity of a that
stock item on that order and 0 if not.
I am calculating remaining stock

Thank you

=INDEX(Data!C2:C500,MATCH(1,(Invent_Count!A2=Data!A2:A500)*(Invent_Count!C1=
Data!B2:B500),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Esrei" <[email protected]> wrote in message
news:[email protected]...
> I have 2 worksheets Data and Invent_Count.
>
> In transit invent I have in A a list of all invertory codes and in row 1
> order nr not shipped
> Worksheet data is in database format with a column for Order_NR an a colmn
> for Invent_Code
>
> In Invent_Count!c2 I want to place a formula that would look in
Data!A2:C500
> match Invent_Count!A2 & Invent_Count!C1 and give me the quantity of a
that
> stock item on that order and 0 if not.
> I am calculating remaining stock
>
> Thank you

Briliant thanks

"Bob Phillips" wrote:

> =INDEX(Data!C2:C500,MATCH(1,(Invent_Count!A2=Data!A2:A500)*(Invent_Count!C1=
> Data!B2:B500),0))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Esrei" <[email protected]> wrote in message
> news:[email protected]...
> > I have 2 worksheets Data and Invent_Count.
> >
> > In transit invent I have in A a list of all invertory codes and in row 1
> > order nr not shipped
> > Worksheet data is in database format with a column for Order_NR an a colmn
> > for Invent_Code
> >
> > In Invent_Count!c2 I want to place a formula that would look in
> Data!A2:C500
> > match Invent_Count!A2 & Invent_Count!C1 and give me the quantity of a
> that
> > stock item on that order and 0 if not.
> > I am calculating remaining stock
> >
> > Thank you
>
>
>