data return based on two criteria

Hello,

I have the following data in excel

A2:A5 - cells are merged and contains partnumber (alpha numeric &
numeric)

C1:AF1 - DATE

C2 - initial stock (number)
C3 - receipts data (number)
C4 - production (number)
C5 = available stock (C2+C3)-C4

Similar data is maintained for 25 partnumbers

I would like to have a sheet where the users can input date &
partnumber and get available stock data.

Your help will be highly appreciated.

Regards,

Jimmy Joseph

Hello,

Help please!

Jimmy Joseph <[email protected]> wrote in
news:[email protected]:

> Hello,
>
> I have the following data in excel
>
> A2:A5 - cells are merged and contains partnumber (alpha numeric &
> numeric)
>
> C1:AF1 - DATE
>
> C2 - initial stock (number)
> C3 - receipts data (number)
> C4 - production (number)
> C5 = available stock (C2+C3)-C4
>
> Similar data is maintained for 25 partnumbers
>
> I would like to have a sheet where the users can input date &
> partnumber and get available stock data.
>
> Your help will be highly appreciated.
>
> Regards,
>
> Jimmy Joseph
>

This may work for you:

=INDEX($A$1:$D$16,MATCH(G8,A:A,0)+3,MATCH(H8,1:1,0))

In Sheet2:
Put the part number in A1
Put the date in B1
Put this in C1:
=INDEX(sheet1!$A:$af,MATCH(a1,sheet1!A:A,0)+3,MATCH(b1,sheet1!1:1,0))

The first match() looks for a part number match--and then drops down 3 rows (to
get the available stock). The second match() looks for a match in dates.

Debra Dalgleish has lots of notes on how to use =Index() and
=index(match(),match()):
http://contextures.com/xlFunctions03.html



Jimmy Joseph wrote:
>
> Hello,
>
> I have the following data in excel
>
> A2:A5 - cells are merged and contains partnumber (alpha numeric &
> numeric)
>
> C1:AF1 - DATE
>
> C2 - initial stock (number)
> C3 - receipts data (number)
> C4 - production (number)
> C5 = available stock (C2+C3)-C4
>
> Similar data is maintained for 25 partnumbers
>
> I would like to have a sheet where the users can input date &
> partnumber and get available stock data.
>
> Your help will be highly appreciated.
>
> Regards,
>
> Jimmy Joseph

--

Dave Peterson

Oops.

Ignore that first formula.

I used it when I was making a small test and I pasted just for the syntax--then
forgot to delete it.

Jimmy Joseph wrote:
>
> Hello,
>
> I have the following data in excel
>
> A2:A5 - cells are merged and contains partnumber (alpha numeric &
> numeric)
>
> C1:AF1 - DATE
>
> C2 - initial stock (number)
> C3 - receipts data (number)
> C4 - production (number)
> C5 = available stock (C2+C3)-C4
>
> Similar data is maintained for 25 partnumbers
>
> I would like to have a sheet where the users can input date &
> partnumber and get available stock data.
>
> Your help will be highly appreciated.
>
> Regards,
>
> Jimmy Joseph

--

Dave Peterson