Consolidated Lab Test Search Macro

My laboratory keeps each price list on a separate sheet inside one Excel workbook. I need all of those lists merged into one authoritative table and a VBA-powered search tool that lets staff quickly look up a test by name, internal code, or accounting code. New tests are usually added just once a month, yet I still want the workbook to refresh itself automatically whenever a sheet is updated—no manual button-pushing and no need to rewrite the macro every time we add a line.

Here is how I picture the finished file:
• One hidden or protected “master” sheet that pulls every row from the individual sheets, deduplicates where necessary, and remains in sync without user intervention.
• A simple front-end (UserForm or worksheet interface) with three search boxes—test name, internal code, accounting code—that returns matching records instantly.
• Clear, well-commented VBA so future analysts can tweak column names or add new fields without breaking the logic.

Everything must stay inside standard Excel/VBA; no external add-ins or separate databases. If you have ideas to make the architecture more robust (for example using dynamic named ranges or Power Query before the macro fires), feel free to propose it as long as the experience for my team remains one-click simple.

When you hand over the project, I’ll need the finished workbook plus a short read-me outlining how the auto-update works and where to adjust parameters if our sheet layout changes.