IF OR With VLOOKUP Returns Error

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
121
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


Excel Formula:
=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)
      )
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,871
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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))
 

Forum statistics

Threads
1,144,154
Messages
5,722,815
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top