What did I do wrong?

[mcintoshmc]

This formula returns "N/A", and for this particular row, it should return "Pending" because the identifier is in the pending hires and transfers page.

=IF(VLOOKUP(A2,'Open Reqs'!A:A,1,FALSE),"ACTIVE",IF(VLOOKUP(A2,'Pending Hires & Transfers'!A:A,1,FALSE),"PENDING",IF(VLOOKUP(A2,Archived!A:A,1,FALSE),"FILLED","CANCELLED")))

 

[mcintoshmc]

I tried this, and all the actives worked, but nothing else.

=IF(VLOOKUP(A2,'Open Reqs'!A:A,1,FALSE)=$A2,"ACTIVE",IF(VLOOKUP(A2,'Pending Hires & Transfers'!A:A,1,FALSE)=$A2,"PENDING",IF(VLOOKUP(A2,Archived!A:A,1,FALSE)=$A2,"FILLED","CANCELLED")))

 

[sandy666]

try from the ribbon:
Formulas - Evaluate Formula

 

[DanteAmor]

If it does not find the value, then it returns error #N/A and it does not continue with the next search.
That's why I use IFERROR(if it does not exist, it returns false)

Try this:

=IF(IFERROR(MATCH(A2,'Open Reqs'!A:A,0),FALSE),"ACTIVE",IF(IFERROR(MATCH(A2,'Pending Hires & Transfers'!A:A,0),FALSE),"PENDING",IF(IFERROR(MATCH(A2,Archived!A:A,0),FALSE),"FILLED","CANCELLED")))

 

[joeu2004]

First, =IF(VLOOKUP(A2,'Open Reqs'!A:A,1,FALSE),... and =IF(VLOOKUP(A2,'Open Reqs'!A:A,1,FALSE)=A2,... do not make sense.

If the value in A2 is not in the range 'Open Reqs'!A:A, the function returns #N/A, so the formula does indeed return #N/A.

You do not even get to the "Pending" and "Filled" tests.

I think you want the following:

=IF(ISNUMBER(MATCH(A2,'Open Reqs'!A:A,0)),"ACTIVE",
IF(ISNUMBER(MATCH(A2,'Pending Hires & Transfers'!A:A,0)),"PENDING",
IF(ISNUMBER(MATCH(A2,Archived!A:A,0)),"FILLED","CANCELLED")))

Unrelated observation.... Instead of A:A, I would use $A$1:$A$1000 or whatever range is sufficient, while reasonably large enough to accommodate additions. I don't know if MATCH is smart enough to avoid searching all 1+ million rows.