VBA function similiar to Vlookup function to extract data from multiple worksheets into 1

Greetings,

Im having difficulties and would like to seek advice from experts in the forum. The situation is as below:

My duty right now is to create a database of material pricing and I already created multiple worksheets in one excel. Here is the problem. I wanted to use Vlookup function on one worksheet, where when I type in the detail I want, it will look up the detail in other worksheets and bring out the information. For example, i have a list of worksheets from 1 to 10. The detail I type in is on different worksheet named worksheet A. How do I can have vlookup function from multiple workbooks into 1 workbook. Maybe using VBA or any function that are available in excel?

Thank you for spending time attending my problem.

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),"") ...

im trying to make a workable database, where all the data were entered at many different workbooks. and by typing just a piece of information, it can extract the relevant data from my workbooks into other workbook. is it possible?