Show dates if multiple Serail numbers are equal

[Dark_Templar]

Hey,

I have a list which lists failures of certain parts ( column G,PN in
Failure sheet) inculding the machine (column B,SN .. Serial Number in
Failure)
and Install Date (Column D in Failure) some other dates.

I also have a sheet "Built list" that includes all built Date for those
SN .. Serial Numbers..but we dont care about that. I have no problem
matching those.

The main sheet (called Query1) includes column A: parts (PN) and coulmn
B:Serial Numbers (SN) and Install Date (and other dates), mentioned
above.

I want excel to give me the install date from the Failure-sheet when
the PN *and* SN from the Query1 sheet match the PN and SN in the
Failure-sheet.

For example:

I assign the Install date to a Serial number by
=INDEX(Failure!D,MATCH(Query1!$B2,Failure!B:B,0))
But this ignores the PN and gives me a wrong date.
I don't know how to match both.

I would be very thankful for any help - I hope this was
understandable..
I added the sheet,so you can play with it


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4604 |
+-------------------------------------------------------------------+

 

[JamesArchibald]

Is it normal/possible to have SN numbers that are the same as each
other? Is it normal/possible to have PN numbers that are the same as
each other?

If either of them are a unique number each time it would be possible to
do a vlookup and an IF formila. If yes, I can advise the code.

James

 

[Toppers]

In J2 and copy down

=IF(B2=INDIRECT("Failure!B"
&MATCH(A2,Failure!G:G,0)),INDIRECT("Failure!D"&MATCH(A2,Failure!G:G,0)),"")

If, as previous posting, PN/SN combinations are unique, then VLOOKUP could
be used.

 

[Dark_Templar]

Thanks for the quick response.

A SN can have multiple PNs
A PN can have mutiple SNs

Unfortunately excel says the function

=IF(B2=INDIRECT("Failure!B"&MATCH(A2,Failure!G:G,0)),INDIRECT("Failure!D"&MATCH(A2,Failure!G:G,0)),"")

contains an error,at the marked spot. To me it looks ok tho...
Whats wrong? :

 

[JamesArchibald]

Yeah that is fine, but can there be two SN's that are the same number i
column B in the Failure sheet. Can there be two PN's that are the sam
number in column G of the Failure sheet

 

[Toppers]

Can't see what's wrong and it worked for me. HOWEVER, I realised it will only
work if a PN occurs only once in Column G (it was late at night when I did
it!) so we need another solution.

You need to match PN/SN pairs to get the date; one way is to add a "helper"
column of PN & SN concatenated in your "Failure" sheet and do a VLOOKUP.

Adding a helper column C in FAILURE(shifting everything right) contain =h2 &
b2 i.e. PN+SN ,copied down, then in colum I of Query1 we put:

=VLOOKUP(A2&B2,Failure!C:E,3,FALSE)

and copy down.

HTH

 

[Toppers]

A second (or is it third) thought:

Make column A the helper, and change VLOOKUP to
=VLOOKUP(A2&B2,Failure!A:E,5,FALSE)