If you know what sheet the detail you are looking for is in but just want to have flexible formula - Use INDIRECT to create second value for VLOOKUP
If you want to search all tables, but the detail you are looking for is just in one of them you can either:
- join tables into one sheet (probably you do not want it, do you?) or use

=IF(ISERROR(VLOKOOP(in first sheet),"",VLOKOOP(in first sheet))&IF(ISERROR(VLOKOOP(in second sheet),"",VLOKOOP(in second sheet))...

in Excel 2007 and next it could be shortened to
=IFERROR(VLOKOOP(in first sheet),"")&IFERROR(VLOKOOP(in second sheet),"") ...