How to user VLOOKUP to cross multiple excel sheets (formula)

Question

Hello,

I have more than 40 sheets in one excel file due to 3 million records. Now, I would like to create a new sheet and specify cell if this value exists in prior 40 sheets. VLOOKUP can work in a single sheet, any suggestion for crossing multiple sheets?

I just wanna a formula because i just statist something.

Thanks,

Cup

Answer 1

Hello:

VLookup ranges do not cross multiple sheets. So, one way around this would be to create 41 columns in a new worksheet (one for the value you are trying to find, and then 40 more for 40 different VLookup commands) and then evaluate the contents of the 40 columns for a match.

For example:

Column 1 of your new worksheet contains the value you are searching for.
Column 2 would contain the VLookup formula for looking at sheet 1 of 40
Column 3 would contain the Vlookup formula for looking at sheet 2 of 40

and so on.

Another way would be to write a VBA lookup subroutine in which you pass it the sheet name and range for that sheet and have it iterate through 40 times.

To verify that Ranges can't cross multiple worksheets, I tried to create a multi-sheet Range in VBA using the Union command, and it worked fine as long as the addresses were in the same worksheet.  As soon as I tried to include ranges in other worksheets, I received an error message.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 2

You mentioned: "Column 2 would contain the VLookup formula for looking at sheet 1 of 40"

But, you said in the very first, this function does not support cross multiple sheets. How do I achieve this?

Thanks,

Cup

Answer 3

Hello:

Yes, you are correct.  Each VLookup command can only search a range that is within ONE worksheet.  Since you have 40 worksheets to search, you must have 40 VLookup commands. (Or, as I mentioned earlier, write a VBA subroutine that you could call 40 times).

The results would be in columns 2 through 41.  Once all the VLookups have run, then you can examine the contents of columns 2 through 41 with another function in column 42 (Column 42 would NOT be a VLookup command, but a command that looks at the contents of columns 2 through 41 to see if any items were found.)

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 4

You can do a Vlookup on multiple sheets, using a this function:


http://www.ozgrid.com/VBA/VlookupAllSheets.htm

---

Ryan Shuell

Answer 5

You may use CHOOSE with VLOOKUP as follows:

=VLookup(YourData,CHOOSE(SheetNum,Range1,Range2,Range3,.....Range254),ColumnNum)

then use Data Table with SheetNum as Input.

Download sample from

www.ExcelExpertTraining.com/download/VLookupChoose.xlsx

Answer 6

Hello All,

sorry for the late reply. First, I'm not familiar with VBA, so I give up this suggestion. Then, if I try to write a VLOOKUP fomula, it looks very complex and uncomfortable. So far, I think I have to write a C# code to produce this report.

I encountered a question is: how to return cell information from Excel. I mean first I need to search if this value exists in the special column and then, I need to return this cell's information such as B2.

So the question is: How can I search this value in a special column? any method is provided by Micorsoft.Office.Interop.Excel?

Second question is: How can I return its value like B2? I would like to know what the cell information of this searching value is.

Thanks,

Cup

Answer 7

Hello Cup:

If you are going to use C# from Visual Studio, then we have some forum members that can help with that process.  As far as the VLookup command, if you follow the formula wizard, it is fairly straightforward.  As I mentioned, you will need to create 40 different VLookup commands if you have 40 worksheet ranges.

One of the tricks of the VLookup is that when you are using the wizard and you enter the Table Array range, press F4, and it will put in absolute addresses for the table, which is necessary if you plan to copy and paste the formula down 2 or more rows.  The table array is the array of values that you are searching to find a match.  The formula below says that you are searching for the value contained in D2, and you are searching a large set of values in range $F$12:$H$22, and when you find a match you are using the 2nd column in the table for the results of the search, and that you want to find an EXACT match (that's the FALSE).

=VLOOKUP(D2,$F$12:$H$22,2,FALSE)

Notice that in the example above, the table array address is $F$12:$H$22, which keeps it absolute if you drag the formula downward.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 8

Thanks again for your continuous following. I try to open a ticket on C# forum, but they said this question/developement is related to Excel, so I was kicked back again.

As I mentioned, I just exampled I have 40 sheets, but actually, I have 120 sheets. So I have to give up to write VLOOKUP fomula, rather than C# development. So, could you please tell me any methods I can search a value in the special column and any methods I can return cell information for the special search result? I mean, if I searched the value in the B2 cell, and it should return B2.

Thanks,

Cup,

Answer 9

Hello Quist Zhang,

I think this reply is good idea, but I'm not familiar with excel fomula and also I have forwarded to develop a utility to achieve this issue. I still think this reply is helpful and can be voted, but to be honest, it is not really solve my concern.

Thanks for all supports.

Cup.