Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Can I VLOOKUP in 7 different worksheets in 1 formula?

144 views
Skip to first unread message

keithobro

unread,
Jan 13, 2008, 1:53:00 PM1/13/08
to
I want to get a master worksheet to check for a candidate's details across 7
other worksheets. If the VLOOKUP fails to find that person on sheet 1, it
tries sheet 2, then sheet 3 and so on.

can this be done? is it a question of replacing the FALSE part with the next
VLOOKUP formula?

Thanks.

Keith

T. Valko

unread,
Jan 13, 2008, 2:57:20 PM1/13/08
to
Do your 7 sheet names follow some sort of sequential pattern like Week1,
Week2, Week3?

--
Biff
Microsoft Excel MVP


"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:8F76EC12-423D-4DDB...@microsoft.com...

keithobro

unread,
Jan 13, 2008, 7:33:01 PM1/13/08
to
Each sheet represents a different interview location with up to 200 names on
each. Why?

Tyro

unread,
Jan 13, 2008, 8:34:08 PM1/13/08
to
Which version of Excel are you using? If Excel 2003 or prior, you are
limited to 7 levels of nesting, in Excel 2007, you have 64 levels of
nesting.

Tyro


"keithobro" <keit...@discussions.microsoft.com> wrote in message

news:8A479FD6-BD41-4791...@microsoft.com...

T. Valko

unread,
Jan 13, 2008, 10:27:40 PM1/13/08
to
> Each sheet represents a different interview location

I guess that means the answer to my question is no?

>Why?

The information you provide will determine what kind of suggestions you'll
get.

Make a list of your sheet names and give this list a defined name:

B1:B7 = list of sheet names = defined name WSList

A1 = lookup value

Try this array formula** :

=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!B1:B7"),A1)>0,0))&"'!A:B"),2,0)

Assumes the table_array on each sheet is in the range A:B.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"keithobro" <keit...@discussions.microsoft.com> wrote in message

news:8A479FD6-BD41-4791...@microsoft.com...

T. Valko

unread,
Jan 13, 2008, 10:38:40 PM1/13/08
to
Correction: I used the wrong range in the COUNTIF function. Should be:

=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!A:A"),A1)>0,0))&"'!A:B"),2,0)

--
Biff
Microsoft Excel MVP


"T. Valko" <biffi...@comcast.net> wrote in message
news:O%23Y1q1lV...@TK2MSFTNGP02.phx.gbl...

keithobro

unread,
Jan 14, 2008, 1:53:01 AM1/14/08
to
Many thanks Biff:

I think I understand. Will try it out when I get to work today.

If i understand correctly, my first task is to list the sheet names, but
where should I put this?

Keith

T. Valko

unread,
Jan 14, 2008, 12:54:13 PM1/14/08
to
You can put things wherever you want them. The ranges/references I used are
just for demonstration purposes.

Suppose your lookup_value was in cell A1. Typically, you'd want the result
of the lookup formula next to the lookup_value so you would probably enter
the lookup formula in cell B1. You can put the list of sheet names anywhere.
If you're using this on some sort of form or report then you'd probably want
the list of sheet names outside of the report or form.

--
Biff
Microsoft Excel MVP


"keithobro" <keit...@discussions.microsoft.com> wrote in message

news:7A83164A-8150-4F8A...@microsoft.com...

keithobro

unread,
Jan 15, 2008, 1:17:02 PM1/15/08
to
Hi biff

Afraid I don't really understand.

Let's say my 7 sheets are named on their tabs:
Alt
Bel
Met
Pan
Pet
Swi

How do I start?

Sorry to be so "dim"!

keith

T. Valko

unread,
Jan 16, 2008, 1:23:08 AM1/16/08
to
Here's a small sample that demonstrates this:

vlookup across multiple sheets.xls 20kb

http://cjoint.com/?bqhmgKwUIV

Select an employee name from the drop down in cell A2 on Sheet1.

Lookup the employee's name and return that employee's manager. The
employee's name could be on any of 6 sheets (but will only appear on 1).

Sheet1 B2 returns the employee's manager
Sheet1 C2 returns the sheet name where that employee's info can be found

--
Biff
Microsoft Excel MVP


"keithobro" <keit...@discussions.microsoft.com> wrote in message

news:8B800840-1554-4201...@microsoft.com...

Gemz

unread,
Jan 17, 2008, 9:42:01 AM1/17/08
to
Hi,

I have a similar issue, i have already posted this question but the person
answering my question hadnt done this before so couldnt provide much
guidance, and since you have can you please provide some guidance?

my previous email:

> > Hi, I have the following 2 formulae and i have been desperately trying
to
> > > make them work but the dont!
> > >
> > > I am looking up the value of column D in 1 worksheet to look across 10/20
> > > different sheets (which are all named) and then return the results as "1,0"
> > > when done into another sheet. So i am just looking up names from column D of
> > > sheet"names" and then looking across 10 sheets, again in the same column - D (dont want it to return anything other than just the name, which will tell me if that is present anywhere witin the 20worksheets.)
> > >
> > > These are the formulae i have tried. the 1st works on the sheets being
> > > called sheets 1,2 etc and not being renamed and the 2nd formula allows you to
> > > name the sheets then refer to them seperatly. i would like to use the 2nd as
> > > i all my sheets are named.
> > >
> > > =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(INDIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B1)>0,0)&"!d2:d100"),1,0)
> > >
> > >
> > >
> > > =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)>0,0))&"!d2:d100"),1,0)
> > >
> > >
> > > I either get the response N/A or VALUE. i dont know if i have got all the
> > > referenes correct, maybe thats why its not working.
> > >
> > > maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is
> > > the range where the lookup should look in.
> > >
> > > I hope im making sense.
> > >
> > > Thanks for your help.


please help!!

T. Valko

unread,
Jan 17, 2008, 2:15:41 PM1/17/08
to
See my reply to your original post

--
Biff
Microsoft Excel MVP


"Gemz" <Ge...@discussions.microsoft.com> wrote in message
news:DFC2375A-5C70-4AE1...@microsoft.com...

ayaz jafri

unread,
Jan 27, 2012, 6:47:25 AM1/27/12
to
I have huge data in 16 worksheets. Now I want to create 17 sheet with vlookup

> On Sunday, January 13, 2008 1:53 PM keithobr wrote:

> I want to get a master worksheet to check for a candidate's details across 7
> other worksheets. If the VLOOKUP fails to find that person on sheet 1, it
> tries sheet 2, then sheet 3 and so on.
>
> can this be done? is it a question of replacing the FALSE part with the next
> VLOOKUP formula?
>
> Thanks.
>
> Keith


>> On Sunday, January 13, 2008 2:57 PM T. Valko wrote:

>> Do your 7 sheet names follow some sort of sequential pattern like Week1,
>> Week2, Week3?
>>
>> --
>> Biff
>> Microsoft Excel MVP


>>> On Sunday, January 13, 2008 7:33 PM keithobr wrote:

>>> Each sheet represents a different interview location with up to 200 names on
>>> each. Why?
>>>
>>> "T. Valko" wrote:


>>>> On Sunday, January 13, 2008 8:34 PM Tyro wrote:

>>>> Which version of Excel are you using? If Excel 2003 or prior, you are
>>>> limited to 7 levels of nesting, in Excel 2007, you have 64 levels of
>>>> nesting.
>>>>
>>>> Tyro


>>>>> On Sunday, January 13, 2008 10:27 PM T. Valko wrote:

>>>>> I guess that means the answer to my question is no?
>>>>>
>>>>>
>>>>> The information you provide will determine what kind of suggestions you'll
>>>>> get.
>>>>>
>>>>> Make a list of your sheet names and give this list a defined name:
>>>>>
>>>>> B1:B7 = list of sheet names = defined name WSList
>>>>>
>>>>> A1 = lookup value
>>>>>
>>>>> Try this array formula** :
>>>>>
>>>>> =VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!B1:B7"),A1)>0,0))&"'!A:B"),2,0)
>>>>>
>>>>> Assumes the table_array on each sheet is in the range A:B.
>>>>>
>>>>> ** array formulas need to be entered using the key combination of
>>>>> CTRL,SHIFT,ENTER (not just ENTER)
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Biff
>>>>> Microsoft Excel MVP
>>>>>
>>>>>
>>>>> "keithobro" <keit...@discussions.microsoft.com> wrote in message
>>>>> news:8A479FD6-BD41-4791...@microsoft.com...


>>>>>> On Sunday, January 13, 2008 10:38 PM T. Valko wrote:

>>>>>> Correction: I used the wrong range in the COUNTIF function. Should be:
>>>>>>
>>>>>> =VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!A:A"),A1)>0,0))&"'!A:B"),2,0)
>>>>>>
>>>>>> --
>>>>>> Biff
>>>>>> Microsoft Excel MVP
>>>>>>
>>>>>>
>>>>>> "T. Valko" <biffi...@comcast.net> wrote in message
>>>>>> news:O%23Y1q1lV...@TK2MSFTNGP02.phx.gbl...


>>>>>>> On Monday, January 14, 2008 1:53 AM keithobr wrote:

>>>>>>> Many thanks Biff:
>>>>>>>
>>>>>>> I think I understand. Will try it out when I get to work today.
>>>>>>>
>>>>>>> If i understand correctly, my first task is to list the sheet names, but
>>>>>>> where should I put this?
>>>>>>>
>>>>>>> Keith
>>>>>>>
>>>>>>> "T. Valko" wrote:


>>>>>>>> On Monday, January 14, 2008 12:54 PM T. Valko wrote:

>>>>>>>> You can put things wherever you want them. The ranges/references I used are
>>>>>>>> just for demonstration purposes.
>>>>>>>>
>>>>>>>> Suppose your lookup_value was in cell A1. Typically, you'd want the result
>>>>>>>> of the lookup formula next to the lookup_value so you would probably enter
>>>>>>>> the lookup formula in cell B1. You can put the list of sheet names anywhere.
>>>>>>>> If you're using this on some sort of form or report then you'd probably want
>>>>>>>> the list of sheet names outside of the report or form.
>>>>>>>>
>>>>>>>> --
>>>>>>>> Biff
>>>>>>>> Microsoft Excel MVP
>>>>>>>>
>>>>>>>>
>>>>>>>> "keithobro" <keit...@discussions.microsoft.com> wrote in message
>>>>>>>> news:7A83164A-8150-4F8A...@microsoft.com...


>>>>>>>>> On Tuesday, January 15, 2008 1:17 PM keithobr wrote:

>>>>>>>>> Hi biff
>>>>>>>>>
>>>>>>>>> Afraid I do not really understand.
>>>>>>>>>
>>>>>>>>> Let's say my 7 sheets are named on their tabs:
>>>>>>>>> Alt
>>>>>>>>> Bel
>>>>>>>>> Met
>>>>>>>>> Pan
>>>>>>>>> Pet
>>>>>>>>> Swi
>>>>>>>>>
>>>>>>>>> How do I start?
>>>>>>>>>
>>>>>>>>> Sorry to be so "dim"!
>>>>>>>>>
>>>>>>>>> keith
>>>>>>>>>
>>>>>>>>> "T. Valko" wrote:


>>>>>>>>>> On Wednesday, January 16, 2008 1:23 AM T. Valko wrote:

>>>>>>>>>> Here's a small sample that demonstrates this:
>>>>>>>>>>
>>>>>>>>>> vlookup across multiple sheets.xls 20kb
>>>>>>>>>>
>>>>>>>>>> http://cjoint.com/?bqhmgKwUIV
>>>>>>>>>>
>>>>>>>>>> Select an employee name from the drop down in cell A2 on Sheet1.
>>>>>>>>>>
>>>>>>>>>> Lookup the employee's name and return that employee's manager. The
>>>>>>>>>> employee's name could be on any of 6 sheets (but will only appear on 1).
>>>>>>>>>>
>>>>>>>>>> Sheet1 B2 returns the employee's manager
>>>>>>>>>> Sheet1 C2 returns the sheet name where that employee's info can be found
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Biff
>>>>>>>>>> Microsoft Excel MVP
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "keithobro" <keit...@discussions.microsoft.com> wrote in message
>>>>>>>>>> news:8B800840-1554-4201...@microsoft.com...


>>>>>>>>>>> On Thursday, January 17, 2008 9:42 AM Gem wrote:

>>>>>>>>>>> Hi,
>>>>>>>>>>>
>>>>>>>>>>> I have a similar issue, i have already posted this question but the person
>>>>>>>>>>> answering my question hadnt done this before so couldnt provide much
>>>>>>>>>>> guidance, and since you have can you please provide some guidance?
>>>>>>>>>>>
>>>>>>>>>>> my previous email:
>>>>>>>>>>>
>>>>>>>>>>> > > Hi, I have the following 2 formulae and i have been desperately trying
>>>>>>>>>>> to
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> please help!!
>>>>>>>>>>>
>>>>>>>>>>> "T. Valko" wrote:


>>>>>>>>>>>> On Thursday, January 17, 2008 2:15 PM T. Valko wrote:

>>>>>>>>>>>> See my reply to your original post
>>>>>>>>>>>>
0 new messages