Lookup function wont ignore blank cells

K

Kquig

New Member
Joined
Jul 8, 2010
Messages
28
Any help on this would be much appreciated.

I've tried many didn't formulas however I keep returning to:

=IF(ISNA(VLOOKUP(C5,Sheet2!B:B,1,FALSE)),"YES","NO")

In the 'C' column on sheet 1 zip codes will be entered, on sheet 2, column 'B' is a list of zip codes. This formula states a 'YES' if the zip code is not found and a 'NO' if it is. The problem with it is if the 'C' column is blank it shows the 'YES' answer, which I don't want.

Is there a change I can make to the formula to have it show no answer if the 'C' cell is blank?

Thank you so very much in advance, I'm pulling my hair out trying to get this to work properly.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Kquig said:
Any help on this would be much appreciated.

I've tried many didn't formulas however I keep returning to:

=IF(ISNA(VLOOKUP(C5,Sheet2!B:B,1,FALSE)),"YES","NO")

In the 'C' column on sheet 1 zip codes will be entered, on sheet 2, column 'B' is a list of zip codes. This formula states a 'YES' if the zip code is not found and a 'NO' if it is. The problem with it is if the 'C' column is blank it shows the 'YES' answer, which I don't want.

Is there a change I can make to the formula to have it show no answer if the 'C' cell is blank?

Thank you so very much in advance, I'm pulling my hair out trying to get this to work properly.
Click to expand...
Maybe:
Code: 
=IF(ISBLANK(C5),"",IF(ISNA(VLOOKUP(C5,Sheet2!B:B,1,FALSE)),"YES","NO"))
 
Upvote 0
Try something like this...

=IF(ISBLANK(C5),"",IF(COUNTIF(Sheet2!B:B,C5)>0,"Yes","No"))
 
Upvote 0
Thank you to all for your help. Joe handled it. But I do appreciate everyones help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
OR function that ignores blanks
Replies
1
Views
441
KRice
K
R
  • Solved
Ignoring the value of a formula in a cell.
Replies
8
Views
304
Royalbloodi
R
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
246
birdman15
B
P
  • Solved
How to return blank cell with if/and functions
Replies
4
Views
499
Peter_SSs
Peter_SSs
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
353
Greenbehindthecells
G

Forum statistics

Threads
1,215,103
Messages
6,123,106
Members
449,096
Latest member
provoking

Share this page

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
Back
Top