Vlookup and Multiple Worksheets
Posted by Ernesto on April 16, 2001 9:29 PM
I have a huge workbook, which has 9 worksheets. Each worksheet has the same type of data, An Account #, Name, Adress, City, State, Zip.
I need to be able to use vlookup by referencing the
account #, but the account # might be on any of the 9 worksheets. I have the formula where I can get the right information when I tell excel exactly in which worksheet to look in, but can I somehow have excel look through all the worksheets by putting something in
I hope I made myself somewhat clear,
Posted by Dave Hawley on April 16, 2001 10:50 PM
I would use a Custom function (UDF) for this. the one below will use the VLOOKUP across ALL sheets until it finds a match.
To use it Push Alt+F11 and go to Insert>Module and paste in the code below:
Function LookAcrossSheets(What, Where As Range, _
Colnum As Integer, TorF As Boolean)
'Written by OzGrid Business Applications
'Uses Vlookup across ALL sheets until a match is found
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ActiveWorkbook.Worksheets
FindIt = WorksheetFunction.VLookup(What, Sheets(.Index).Range(Where.Address), Colnum, TorF)
If FindIt <> "" Then Exit For
LookAcrossSheets = FindIt
Push Alt+Q to return to Excel.
Push Shift+F3, scroll down to "User defined" then select "LookAcrossSheets". Use the fuction in the same way you would a normal VLOOKUP.
This will look in all sheets until it finds 2564 in D1:D1000 and return the value in the same row in Column E
OzGrid Business Applications
Posted by Ernesto on April 17, 2001 7:38 AM
Thanks! I'll try this out and report back
whether or not I can do it (I'm rather dumb ;) ).
Posted by Ernesto on April 17, 2001 8:22 AM
Thanks again for the help. I am trying your code right now and I am not getting results. I
think I need to explain what I have to do.
I have one workbook with 9 sheets. This workbook
contains names and addresses, account #'s, etc.
On another workbook, I only have account #'s.
What I need is to extract all the information from
the workbook that has all the info, and put it into the workbook that only contains the account
When I used vlookup, I was able to use this:
Where vlookup will take the account numbers that
are in cells a1 - a10, use that number to search
in the acq.xls file, but I need to tell excel in
which sheet to look in. Since the account Number
can be in any of 9 sheets, I want a way to not
Is it possible, even with your code?
Posted by Aladin Akyurek on April 17, 2001 9:02 AM
This might be also of interest to you.
Posted by Ernesto on April 17, 2001 9:28 AM
I looked at your post, and I believe I followed
everything until you created the TOT table and
then wrote this:
Now I create a lookup table of all my lookup tables on Sheet 1:
2 4 Table1
5 7 Table1
I name this range TOT.
I was lost there, let alone the formula. I tried
to follow it, but I didn't get it.
Can you clarify a bit more? Thanks!
This might be also of interest to you. 11664.html Aladin
Posted by Aladin Akyurek on April 17, 2001 10:52 AM
BTW, "5 7 Table1" should be "5 7 Table2."
The idea I am thinkering about is that your account numbers may have a regular partitioning across your worksheets. If so, the proposal I referred to might work. I've just sent you the example file via e-mail.
Are your account numbers numeric or alphanumeric?
Posted by neitzl on April 17, 2001 11:07 AM
Yah, I figured it should be Table2.
My account #'s are alphanumeric, the alpha
part is in the beginning of the account # though,
so it does sort well.
Thanks for the email, I'll go checkfor it right
now! Ernesto BTW, "5 7 Table1" should be "5 7 Table2." The idea I am thinkering about is that your account numbers may have a regular partitioning across your worksheets. If so, the proposal I referred to might work. I've just sent you the example file via e-mail. Are your account numbers numeric or alphanumeric? Aladin ============= : Aladin,
Posted by Marc on April 17, 2001 2:18 PM
Vlookup with Cell Reference in Place of Table
Is it possible to refer to a cell rather than a specific table when using vlookup? For example, the typical vlookup might look like:
I want to do something like:
In this case, A5 might be equal to "Table"...Unfortunately when I try, the A5 comes back as a text and the formula fails (#N/A)
Posted by Aladin Akyurek on April 17, 2001 2:35 PM
Re: Vlookup with Cell Reference in Place of Table
If A5 contains a range as value like $C$4:$F$16 or a name referring to a range, then you can use