Multiple IFs & VLOOKUPS across any sheet, not all

[grumiocantat]

I have a Google Docs Spreadsheet that doesn't allow IFS functions. My function is as follows:

=IF(VLOOKUP(C304,'PM1 Responses'!B:G,6,)="P","P",IF(VLOOKUP(C304,'PM2 Responses'!B:G,6,)="P","P",IF(VLOOKUP(C304,'PM3 Responses'!B:G,6,)="P","P","-")))

I'm trying to lookup IF the person is marked as P in *any* of the three different sheets, whether marked in none, one, two or all three sheets. They may also be in any of the three sheets, not all of them.

What I've found is that this function works for the first two IFs if the person is listed in both sheets. But if they're not listed in the first sheet, the VLOOKUP returns an error.

Also, this function doesn't return the lookup for the third sheet even if they're listed in all sheets...

Appreciate your help!

[Jacc]

Try something like this

=IF(OR(IFERROR(IF(VLOOKUP(C304,'PM1 Responses'!B:G,6,)="P",TRUE),FALSE);IFERROR(IF(VLOOKUP(C304,'PM2 Responses'!B:G,6,)="P",TRUE),FALSE);IFERROR(IF(VLOOKUP(C304,'PM3 Responses'!B:G,6,)="P",TRUE),FALSE));"P";"-")

[grumiocantat]

Okay, so this kind of works. But for some reason there is an issue with the PM3 Responses sheet.

When I do this, the 1st and 2nd work fine when there is either a P or no P in column G (the 6th column of the VLOOKUP), but for PM Responses 3 it will not return P when there is a P in column G (6th column)... Am I missing something? I have run a match to ensure that the name in the lookup is exactly the same, so am a bit baffled.

[etaf]

You have three conditions

If it cannot find the lookup in C304 in the sheets - thats a #N/A error
if it can find the lookup but not P = 0
if it can find the lookup P = P
so you will get

#N/A or False or True in your results

and thats the issue I suspect

will google docs use IFERROR()
if
so
=IF(OR(IFERROR(VLOOKUP(C3,'PM1 Responses'!B:G,6,)="P",FALSE),IFERROR(VLOOKUP(C3,'PM2 Responses'!B:G,6,)="P",FALSE),IFERROR(VLOOKUP(C3,'PM3 Responses'!B:G,6,)="P",FALSE)),"P","-")

Now if the result is #N/A - IE the value in C304 cannot be found in the sheet - then that will be changed to FALSE
if it can find the lookup but column G does not = P that is also a FALSE
Then if it can find a P thats a True
and by using a OR
we look to see if a TRUE exists in any of the sheets to return a P

[grumiocantat]

Okay, I had tried this without the OR before, so yep, on the right track. It is still however not working (and really pissing me off haha).

There is no #N/A error with your formula etaf. Here's what I'm doing:
- When I put a P or nothing in Sheet 1 and 2, it works fine.
- When I put nothing in Sheet 1 and 2, and a P in Sheet 3, it returns "-"

All functions, IF, VLOOKUP and OR are acceptable. There are no limitations to what I can see here. So your equation should definitely work. It's basically saying if any of those OR logical tests are TRUE, then return "P", otherwise return "-"

I have now checked all sheet names and even rewritten your formula, but no luck

[etaf]

There is no #N/A error with your formula etaf.

no there wont be as thats whats the IFERROR is checking for and if finds then returns FALSE anyway

its working fine in excel - so its either an issue with your data - does not appear to be with the changes you have made or a google docs issue

do you have excel at all to at least try

see attached
Cell C and D row 3 has the code

[etaf]

just set up the same in google docs
using sheet3.4,5 and it works

for some reason i cant change the sheet name