Articles

VLOOKUP & Multiple Worksheets

May 28, 2009 at 09:33:13
Specs: Windows XP

I am using a shared workbook and cannot use VBA functions to use VLOOKUP for multiple sheets.

I tried nesting IF Formulas but keep getting errors. Does anyone know a way to lookup information on multiple sheets in a workbook without using a VBA Function.


See More: VLOOKUP & Multiple Worksheets

Report •


#1
May 28, 2009 at 10:13:58

I think we need a little more detail as to what to you are trying to do.


Report •

#2
May 28, 2009 at 10:26:12

There are 5 sheets in a shared workbook. Each sheet is maintained by a seperate person. I've created a VLookup that pulls all the information into a single sheet, by assigning ID numbers to each item and doing a lookup for Sheet A for IDS 1-500. Then a Vlookup for Sheet B IDs 501-1000, etc. The problem is that if someone adds item 501 to Sheet A a formula changes the starting # on Sheet B to 502 but because the VLookup is looking for ID 501 in Sheet B and results in an error. If there was a way to look for an id on any of the 4 sheets I wouldn't have to redo the lookup everytime anyone adds something.

Currently I added in blank rows with ID #'s so people can add without messing up the VLOOKUP. It is a poor solution though.


Report •

#3
May 28, 2009 at 11:27:11

I'm not going to take credit for this (other than the research part) and I'm not going to try and explain it. I'll simply say that this array formula seems to work:

=VLOOKUP(C2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A20"),C2)>0),0))&"'!A1:B20"),2,0)

Use Ctrl-Shift-Enter

Where:

- C2 is the lookup_value

- MySheets is a named range that refers to a list of sheet names,

      e.g. ={"Sheet1";"Sheet2";"Sheet3"}

- A1:A20 is the range (common to all sheets) in which you expect to find the lookup_value

- A1:B20 is the lookup_array


Report •

Related Solutions

#4
May 28, 2009 at 12:28:17

As long as you only have 5 sheets, you can use a Nested IF. However, once you hit the 7 Nest limit, you'll need to use the array formula above.

=IF(ISNA(VLOOKUP(C2,Sheet1!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet2!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet3!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet5!A1:B2,2,0),VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet3!A1:B2,2,0)),VLOOKUP(C2,Sheet2!A1:B2,2,0)),VLOOKUP(C2,Sheet1!A1:B2,2,0))

If you keep getting errors, it's probably due to the placement of the parenthesis.


Report •

#5
May 28, 2009 at 14:43:52

Thanks for the help. The second option worked with the nested formula not the ranges. Couldn't get ranges to give me anything except errors (I did name my ranges so not sure why the issue). But thanks all.

Report •

#6
May 28, 2009 at 16:36:56

There was no need to name your ranges.

You were supposed to create a named range that contain only the sheets names like in my example.

In other words, if your sheet tabs had names like Sales, Debits, and Income, then In the Refers to box you would type:

={"Sales";"Debits";"Income"}

and give it a name such as MySheets

The INDIRECT function would then use this array of sheet names to reference ranges such as Sales!A1:A20, Debits!A1:A20, etc.

I built a 5 sheet workbook and the formula was able to pull data from all 5 sheets as advertised.


Report •

#7
May 28, 2009 at 16:39:50

P.S. I hope you also noticed that this is an array formula and needs to be entered by using Ctrl-Shift-Enter.

It will return errors if you don't use Ctrl-Shift-Enter every time you make any change in the formula bar.


Report •


Ask Question