Can someone clue me in on the best way to get a VLOOKUP formula to work with a 3-D range, short of replicating the ranges in a single sheet?
Thanks
Steve
Can someone clue me in on the best way to get a VLOOKUP formula to work with a 3-D range, short of replicating the ranges in a single sheet?
Thanks
Steve
Here is a function that I have that should do it. I don't really remember where I got this code or if I did it myself. Sorry about the long function name, you can change the code and shorten it.
Function TSASVLOOKUP(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Range_look As Boolean)
'This function looks up across all sheets in a workbook.
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
TSASVLOOKUP = vFound
End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks