vlookup for different ranges
iam confussed. it is macro?
"Joel" wrote:
You can do it with the custom function below. Call it like normal VLOOKUP
except the following:
1: Put Range indouble quotes
2: Don't use False/True (4th parameter). Function makes it FALSE which is
an exact match
=PAGEVLOOKUP(9,"A1:B4", 2)
Function PageVLOOKUP(lookup_value, _
table_array, col_index_num)
SheetArray = Array("Sheet1", "Sheet2", _
"Sheet3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8", "Sheet9", "Sheet10")
On Error Resume Next
For Each shname In SheetArray
Set lookuprange = Sheets(shname). _
Range(table_array)
PageVLOOKUP = WorksheetFunction. _
VLookup(lookup_value, _
lookuprange, col_index_num, _
False)
If PageVLOOKUP < "" _
Then Exit Function
Next shname
PageVLOOKUP = "#N/A"
End Function
"hitesh" wrote:
hi
i have different database in different sheets i.e sheet1, sheet2, sheet3,
sheet4, sheet5...............sheet10,
in sheet 11 i used vlookup formula, i wnat to use the vlokup range for all
the sheet, if the value is not find in sheet 1 than find in sheet 2 or in
sheet 3 or in sheet4 means upto sheet 10 .
vlookup is possible for all the above sheet, is it possible.
helps needs
regards
|