Conditional searching

[chrism216]

Hi all,
I have 2 databases. One is historical data of the maintenance of several devices, the other one is historical data of stolen items from those devices.
What i want to do is the following. For any given time when i know a theft happened, i want to know who was the last person to perform maintenance on that device, and populate the data of stolen items with that column. Prettysimple! I cant get it to work though... Ive looked up for alternatives with offset, index, max, but cant get it to work.

Any help will be appreciated!

To make it simple lets say the databases look like this:

Maintenance:
Column A: Device ID
Column B: Date of last maintenance
Column C: Name of technician who performed last maintenance.

Theft:
Column A: Device ID
Column B: Date when theft was discovered
Column C: Name of technician who discovered theft.

 

[Claus Busch]

Hi Chris,

Am Tue, 6 Aug 2013 10:25:15 -0700 (PDT) schrieb (e-mail address removed):

Maintenance:
Column A: Device ID
Column B: Date of last maintenance
Column C: Name of technician who performed last maintenance.

Theft:
Column A: Device ID
Column B: Date when theft was discovered
Column C: Name of technician who discovered theft.

try:
=INDEX(Maintenance!$C$2:$C$1000,MATCH(A2&"#"&B2,Maintenance!$A$2:$A$1000&"#"&Maintenance!$B$2:$B$1000,1))
and enter the array formula with CTRL+Shift+Enter.
The ranges in the formula may not be larger than the range in the sheet.


Regards
Claus B.