find a value at an intersection?

[Guest]

I have the following data:

I need to lookup the part number and trancode in Worksheet2 and return the
quantity to worksheet1.

Worksheet1
A B C D E F G H I J
TraneCodes 2 6 8 12 28 36 54
60........................
1 PART NUMBER
2 003-0623-00
3 005-0030-00
4 005-0168-00
5 005-0320-00
6 005-0420-00
7 005-0539-00
8 005-0798-01
9 005-7013-00


Worksheet2:

A B C D
1 Part# TranCode RecvPlt Qty
2 003-0018-01 28 7 10
3 003-0018-01 40 7 10
4 003-0021-00 8 54 25
5 003-0021-04 4 63
6 003-0021-04 6 11 66
7 003-0021-04 11 1
8 003-0059-00 4 649
9 003-0059-00 22 22

 

[Bob Phillips]

=IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
$2:$B$9),0)))

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

copy down and across, but I get no matches with your data.

--
HTH

Bob Phillips

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

 

[Guest]

Thank Bob - This doesn't reference my quantity column though?
I need it to return the quantity of parts when both conditions agree.

 

[Bob Phillips]

My mistake

=IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
,INDEX(Sheet2!$D$1:$D$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
$2:$B$9),0)))


--
HTH

Bob Phillips

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

 

[Guest]

Hi Ellen,

I would try hlookup() to search for "TraneCodes",
(add a extra Row below TraneCodes to indicate the row number);
use vlookup() to search for the Partnumber and use the "row-result" from
hlookup() as ofset ...

Clear enough?