vlookup for different ranges

[Guest]

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

 

[Guest]

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

 

[Guest]

iam confussed. it is macro?

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

 

[Guest]

Yes it is a macro. It simply uses the same function as the worksheet
function but search more than one worksheet. there arre two type of Macro in
excel. Sub (subroutines) and Functions. Functions behave like all the
standard library Functions such a VLOOKUP(), SUM(), etc , but can be written
to perform just about anything you can imagine and more.

To add macro
1) go to Tools Menu - Macro - Visual Basic Editor
2) VBA menu Insert Module
3) Paste code below

Make sure security level is set to medium which will ask you if you want to
run macros when the workbokk is opened.
1) go to worksheet Tools Menu - Macro - Security.
2) Set level to Medium
3) You need to close workbook and re-open if you change the security level
before the macro will run

 

[Guest]

dear
joel
i dont want to use macro.

Yes it is a macro. It simply uses the same function as the worksheet
function but search more than one worksheet. there arre two type of Macro in
excel. Sub (subroutines) and Functions. Functions behave like all the
standard library Functions such a VLOOKUP(), SUM(), etc , but can be written
to perform just about anything you can imagine and more.

To add macro
1) go to Tools Menu - Macro - Visual Basic Editor
2) VBA menu Insert Module
3) Paste code below

Make sure security level is set to medium which will ask you if you want to
run macros when the workbokk is opened.
1) go to worksheet Tools Menu - Macro - Security.
2) Set level to Medium
3) You need to close workbook and re-open if you change the security level
before the macro will run