data return based on two criteria

[Jimmy Joseph]

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

 

[Jimmy Joseph]

Hello,

Help please!

 

[Dave Peterson]

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

 

[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.