IF OR With VLOOKUP Returns Error

[wsnyder]

Hi All,

Using Microsoft365,

I have an error I dont understand
In the formula, the account I am searching does not exist on the first sheet, so the lookup should be on the second sheet, but the formula is returning #N/A

I definitely see the account on the second sheet so I don't understand the problem.

Thanks
-w

=IF(OR(
               ISERROR(VLOOKUP(P13,SalesOps!$B$2:$O$5166,14,FALSE)),
               VLOOKUP(P13,SalesOps!$B$2:$O$5166,14,FALSE)="Research"
              ),
        VLOOKUP(P13,ACE_Signup!$A$2:$C$396232,3,FALSE),
        VLOOKUP(P13,SalesOps!$B$2:$O$5166,14,FALSE)
      )

 

[Joe4]

You have told it what to do if it does not find the value on "SalesOps", but haven't accounted for the situation of it also not being found on "ACE_SignUp".
That is most likely what is happening, is that your initial OR is being met, so it is looking for it on "ACE_Signup" and not finding it.

Remember, the values must match EXACTLY. Something as simple as an extra space can cause it not to match.
Also, you cannot match "numbers entered as text" to "numbers entered as numbers". You can only compare similar data types.
So "123" (entered as text) will not match the number 123.

If you can still not get it to work, please create a post a small example of your data that captures the issue.

 

[jasonb75]

If it is not in the first sheet then the test to see if it is equal to "Research" will still give you an error and cause the entire formula to fail because you haven't trapped that part.
This should work

=IF(IFERROR(VLOOKUP(P13,SalesOps!$B$2:$O$5166,14,FALSE)="Research",1),
              VLOOKUP(P13,ACE_Signup!$A$2:$C$396232,3,FALSE),
        VLOOKUP(P13,SalesOps!$B$2:$O$5166,14,FALSE))