Back to: Excel Custom Function/Formulas
SEE ALSO: VLOOKUP | 5 Condition Vlookup | 4 Condition Vlookup | 3 Condition Vlookup | 2 Condition Vlookup | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column || Hlookup Formula || Left Lookup in Excel || Excel Lookup Functions | Multi-Table Lookup
This UDF was written by myself to take the place of
when you need to look across ALL the Worksheets in the active Workbook.
The Custom Excel Functions
Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num as Integer, Optional Range_look as Boolean) '''''''''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid.com 'Use VLOOKUP to Look across ALL Worksheets and stops _ at the first match found. ''''''''''''''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet Dim vFound On Error Resume Next For Each wSheet In ActiveWorkbook.Worksheets With wSheet Set Tble_Array = .Range(Tble_Array.Address) vFound = WorksheetFunction.VLookup _ (Look_Value, Tble_Array, _ Col_num, Range_look) End With If Not IsEmpty(vFound) Then Exit For Next wSheet Set Tble_Array = Nothing VLOOKAllSheets = vFound End Function
To use this code do this:
1. Push Alt+F11 and go to Insert>Module
2. Copy and paste in the code.
3. Push Alt+Q and Save.
Now in any cell put in the Function like this:
Where "Dog" is the value to find
" " C1:E20 is the range to look in the first column and find "Dog"
" " 2 is the relative column position in C1:E20 to return return our result from.
" " FALSE (or ommited) means find and exact match of "Dog"
In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. You can find the UDF (VLOOKAllSheets) in the Paste Function dialog (Shift+F3) within the "Function category" of "User Defined".
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates
FREE Excel Help