=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0))

which is an array formula, so commit with Ctrl-Shift-Enter




RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear All,
Please find the first sheet as follows:
PO# Part Number Qty Purchased
Qty
Received
4734LA004 3-1439-6 6
by formula?

While the second sheet as follows:
PO# Part Number Qty Received
Date of Received
4734LA004 3-1439-6 1
1Mar05
4734LA004 3-1439-6 2
10Mar05
4734LA004 3-1439-6 1
1 April05

how is the right coding for formula : to get the result in Sheet 1, column
Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match
function, still not able to solve...
the Match Criteria: that PO# AND ("&") Part Number is must be equal
between
sheet 1 and sheet 2.

Need your help, plz :)

Brgds,andri

Dear Bob,
The result of the formula still 1ea instead of 4ea.
due to that formula only find the first ROW(second parameter in index
formula) Sheet2!C2:C100.
Please advice...

brgds,andri

"Bob Phillips" wrote:

=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0))

which is an array formula, so commit with Ctrl-Shift-Enter




RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear All,
Please find the first sheet as follows:
PO# Part Number Qty Purchased
Qty
Received
4734LA004 3-1439-6 6
by formula?

While the second sheet as follows:
PO# Part Number Qty Received
Date of Received
4734LA004 3-1439-6 1
1Mar05
4734LA004 3-1439-6 2
10Mar05
4734LA004 3-1439-6 1
1 April05

how is the right coding for formula : to get the result in Sheet 1, column
Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match
function, still not able to solve...
the Match Criteria: that PO# AND ("&") Part Number is must be equal
between
sheet 1 and sheet 2.

Need your help, plz :)

Brgds,andri

Sorry, I don't understand. Where does 1ea and 4ea figure in all this?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear Bob,
The result of the formula still 1ea instead of 4ea.
due to that formula only find the first ROW(second parameter in index
formula) Sheet2!C2:C100.
Please advice...

brgds,andri

"Bob Phillips" wrote:

=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0))

which is an array formula, so commit with Ctrl-Shift-Enter




RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear All,
Please find the first sheet as follows:
PO# Part Number Qty Purchased
Qty
Received
4734LA004 3-1439-6 6
by formula?

While the second sheet as follows:
PO# Part Number Qty Received
Date of Received
4734LA004 3-1439-6 1
1Mar05
4734LA004 3-1439-6 2
10Mar05
4734LA004 3-1439-6 1
1 April05

how is the right coding for formula : to get the result in Sheet 1,
column
Qty Received to reflect 4ea. Has tried Sum, frequency, index, and
match
function, still not able to solve...
the Match Criteria: that PO# AND ("&") Part Number is must be equal
between
sheet 1 and sheet 2.

Need your help, plz :)

Brgds,andri

Dear Bob,
Please notice in sheet2, there are three times of receiving period of the
parts purchased.

So this is the scenario, we bought 6ea (Quantity Ordered), and have received
3 times (1ea, 2ea, 1ea) in different RECEIVED date.

So the purpose to know, there is BACK ORDER of 2ea.

Thank you

"Bob Phillips" wrote:

Sorry, I don't understand. Where does 1ea and 4ea figure in all this?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear Bob,
The result of the formula still 1ea instead of 4ea.
due to that formula only find the first ROW(second parameter in index
formula) Sheet2!C2:C100.
Please advice...

brgds,andri

"Bob Phillips" wrote:

=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0))

which is an array formula, so commit with Ctrl-Shift-Enter




RP
(remove nothere from the email address if mailing direct)


"Andri" wrote in message
...
Dear All,
Please find the first sheet as follows:
PO# Part Number Qty Purchased
Qty
Received
4734LA004 3-1439-6 6
by formula?

While the second sheet as follows:
PO# Part Number Qty Received
Date of Received
4734LA004 3-1439-6 1
1Mar05
4734LA004 3-1439-6 2
10Mar05
4734LA004 3-1439-6 1
1 April05

how is the right coding for formula : to get the result in Sheet 1,
column
Qty Received to reflect 4ea. Has tried Sum, frequency, index, and
match
function, still not able to solve...
the Match Criteria: that PO# AND ("&") Part Number is must be equal
between
sheet 1 and sheet 2.

Need your help, plz :)

Brgds,andri