FREE Excel STUFF

Search

Excel Help. Popular

NEW! Multiple Excel Search & Links

Excel Formulas

Excel Macros

Excel Newsletter

PRODUCTS

Up to $139.00 FREE!

Categories & Search

Excel Templates

Excel Add-ins

Excel Training

More....

OTHER

Excel Development

Vlookup Across Excel Worksheets

NEW! More Books..

advanced search! Free Help! Add Excel Answers & Search To Your Google Toolbar Details

Custom/User Defined Function/Formula to Vlookup In All Sheets in an Excel Workbook

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel Custom Function/Formulas . Got any Excel/VBA Questions? Free Excel Help

This UDF was written by myself to take the place of VLOOKUP 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:

=VLOOKAllSheets("Dog",C1:E20,2,FALSE)

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

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 special@ozgrid.com 31 days after purchase date.