How can I create vlookup to find data in multiple tables on different excel
sheets?

You can have a multi range table array in the one worksheet (not necessarily
the same worksheet as the formula) but the ranges must be in the same column.
Simply separate the ranges with a colon. Does not work with multiple
worksheets.

=VLOOKUP(A1,Sheet2!$A$1:$A$6:Sheet2!$A$13:$A$24:Sh eet2!$A$29:$A$43,1,FALSE)

If table array ranges are on multiple worksheets then it is possible to use
IF(ISNA and use multiple nested if statements. However, only works with exact
match parameter otherwise will never get #N/A due to finding nearest match.
Also can get very complex with multiple If's.

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$6,1,FALSE)),VLO OKUP(A1,Sheet3!$C$10:$C$36,1,FALSE),VLOOKUP(A1,She et2!$A$1:$A$6,1,FALSE))

Having said that, I think that you would be wise to try to find a solution
whereby you can get a copy of the table array all together in a contiguous
range. With complex formula solutions you can get it all working properly and
a small worksheet change can through everything in to disarray.

--
Regards,

OssieMac

You can't do that with vlookup. Since yo are posting on the Programming
website are yo interesdted in doint this in a macro?

You probably want to set up a table of all the table you want to search in a
master worksheet. Or the name of each of the table if you add each table
range as a named range on the worksheet. Let me know what you have setup.

"Atul Gupta" wrote:

How can I create vlookup to find data in multiple tables on different excel
sheets?

From OzGrid.com

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 InsertModule
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.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joel" wrote:

You can't do that with vlookup. Since yo are posting on the Programming
website are yo interesdted in doint this in a macro?

You probably want to set up a table of all the table you want to search in a
master worksheet. Or the name of each of the table if you add each table
range as a named range on the worksheet. Let me know what you have setup.

"Atul Gupta" wrote:

How can I create vlookup to find data in multiple tables on different excel
sheets?