Can I VLOOKUP in 7 different worksheets in 1 formula?

[keithobro]

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]

Do your 7 sheet names follow some sort of sequential pattern like Week1,
Week2, Week3?

 

[keithobro]

Each sheet represents a different interview location with up to 200 names on
each. Why?

 

[Tyro]

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

 

[T. Valko]

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)

 

[T. Valko]

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)

 

[keithobro]

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]

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.

 

[keithobro]

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]

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

 

[Gemz]

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:


please help!!

 

[T. Valko]

See my reply to your original post

 

[ayaz jafri]

I have huge data in 16 worksheets. Now I want to create 17 sheet with vlookup