Multisearch in Excel - 2 separate workbooks

[Emil Sudak]

Hi guys

I having trouble filtering and searching in large data spreadsheet.
Here is how it looks like

btt.JPG


Now those Job No(BTT) I have to find in over 17000 other Job No.
Also those 17000 job No. have their technicians names and it looks like this

target.JPG


This data is generated on two separate spreadsheets
I need to find to whom those Job No (BTT) belongs to.

Any help on this would be much appreciated.
Thanks

[Special-K]

Maybe

in Sheet1!F2
=INDEX(Sheet2!C$1:C$1000,MATCH(Sheet1!C$2,Sheet2:D$2:D$1000,0),1)

[Emil Sudak]

I don't know if I did something wrong but this is what I got in return.
This is obviously sheet 1 and sheet2 looks like the one in image above in my first post.

returned result.PNG

[Emil Sudak]

Ok i figured out where was an error

=INDEX(Sheet2!C$1:C$1000,MATCH(Sheet1!C$2,Sheet2:D$2:D$1000,0),1)

changed it to exclametion mark and now this is what im geting as a result. Its giving me same surname for all Job No.

same surname.PNG

Any ideas ?

Also i got the feeling that it will not return more than one surname per Job No (BTT) and i know for fact there some of them belonges to more than one surname. First one in example matches 2 surnames. and its not the one on the picture.

[Emil Sudak]

ok i got some results with that
its giving me the names and most important correct names but as i was expecting only one per JOB NO (BTT) and its always the first one found.
Formula look like that at the moment.

=INDEX(Sheet2!C$2:C$18000,MATCH(Sheet1!C2,Sheet2!D$2:D$18000,0),1)