Nested IF help please!

[EmpireOfLight]

Hi, I have a formula that checks to see if a value in one column exists in another column on a different sheet.

=IF(ISERROR(VLOOKUP(A2,stephanie!A:A, 1, FALSE)),FALSE,"stephanie" )

I want to check additional sheets (potentially many) such as sue, bob, mary, etc. if the value isn't found in stephanie, and continue through the entire list of names.

Right now the formula works, but only for one person.

Thanks for any help.

 

[yky]

What do you want the function to return if a name is found in more than one sheet?

 

[EmpireOfLight]

What do you want the function to return if a name is found in more than one sheet?

The names (bob, mary, etc) are just the names of the other sheets.
The values will be unique, and each sheet will have its own values. So mary might have 1,2,3,4 in the column I'm searching, and bob might have 5,6,7,8 in that column.

So to answer your question, it won't find a name, only numbers, and the numbers won't ever appear on more than one sheet.

Hope this makes sense.

 

[yky]

1 IF(ISERROR(VLOOKUP(A2,stephanie!A:A, 1, FALSE)),FALSE,"stephanie" )
2 IF(ISERROR(VLOOKUP(A2,sue!A:A, 1, FALSE)),FALSE,"sue" )
3 IF(ISERROR(VLOOKUP(A2,bob!A:A, 1, FALSE)),FALSE,"bob" )

Replace the second FALSE in eq. 1 with eq.2. So, you get:

IF(ISERROR(VLOOKUP(A2,stephanie!A:A, 1, FALSE)),IF(ISERROR(VLOOKUP(A2,sue!A:A, 1, FALSE)),FALSE,"sue" ),"stephanie" )

Then, replace the second FALSE in sue with eq. 3, and so on, and so forth.

 

[EmpireOfLight]

Thanks YKY, that's exactly it.

 

[FDibbins]

Might be simpler to do a COUNTIF() across the sheets, and return a value depending if the answer is 1 or 0?

1. create a list of all sheet names (you can add to this later if needed or also remove names.)
2. give that list a range name (I used Tabs)
3. Use this=SUMPRODUCT(COUNTIF(INDIRECT("'"&Tabs&"'!A2:A1000"),C2))

Also, if you have 2007 or later, yky's formula can be shortened to this...
=IFERROR(VLOOKUP(A2,stephanie!A:A, 1, FALSE),VLOOKUP(A2,sue!A:A, 1, FALSE))
or...
=IFERROR(VLOOKUP(A2,stephanie!A:A, 1, 0),iferror(VLOOKUP(A2,sue!A:A, 1, 0),VLOOKUP(A2,dan!A:A, 1, 0))