Formula IFERROR then Blank

pure vito

Board Regular
Joined
Oct 7, 2021
Messages
180
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Might anyone be able to assist here please I have 3 formulas each returning #N/A if the data is not found can anyone amend this to show blank rather than #N/A if the data is not found,

I have tried but I keep getting the same error,

Thanks in advance,

Excel Formula:
=IF(VLOOKUP(A12,Available!$C$1:O110,MATCH(Rolecheck!$C$1,Available!$C$1:$O$1,0),0)="Yes","Yes","")

Excel Formula:
=IF(C12="YES",IF(F12="YES","Available",""),"")

Excel Formula:
=IF(G12="Available",A12,"")
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The Sheet is "Rolecheck" from row 12 downwards it shows #N/A in Columns C ,G ,H
 
Upvote 0
The Sheet is "Rolecheck" from row 12 downwards it shows #N/A in Columns C ,G ,H
Thanks. The problem is you have done something funny to the row numbers. Your formula in C2 refers to the cell in A15. It should refer to the cell in the same row: A2

1674042031324.png


BTW, it is a bad idea to use the sheet name that the formula is on in a formula like that. You should just use $C$1 not Rolecheck!$C$1
 
Upvote 0
That's an error on my part I pasted it there the cells below should have the correct reference, sorry if that's thrown you,
 
Upvote 0
The cells below have the correct row reference but they do not have the IFNA() function.
Change that A15 in C2 to A2 and then copy that formula down the column
 
Upvote 0
I did try it peter and as you will see when you paste the formula down it removes the "yes" I left it without your solution so you can see what's happening when the formula is applied
 
Upvote 0
as you will see when you paste the formula down it removes the "yes"
Not for me it doesn't.

pure vito_1.xlsm
ABC
1LinefeedReach Truck
2Jane downYes
3Fred Flintstone 
4Bobby CatYes
5Aaron CopeYes
6Kaspars RiskinsYes
7James KrasniqiYes
8Raja Ahmed 
9Zakir Mahroof 
10Vito Mapp 
11Sarjit Flora 
120 
130 
140 
150 
160 
Rolecheck
Cell Formulas
RangeFormula
A2:A102A2=BIW!F10:F110
C2:C16C2=IFNA(IF(VLOOKUP(A2,Available!$C$1:O113,MATCH(Rolecheck!$C$1,Available!$C$1:$O$1,0),0)="Yes","Yes",""),"")
Dynamic array formulas.
 
Upvote 0
Solution
I don't know what's changed peter but it seems to be working now that great i'm just sorry it's taken up so much of your time, Thank You,
 
Upvote 0
Glad we got there in the end. 😎

Two things I suggest though:
  1. Remove the reference to Rolecheck in the formula since that is the sheet the formula is on. As I mentioned earlier, it is a bad idea. (It can lead to errors in some circumstances)
  2. Probably also best to fix the rows in the 'Available' lookup range.
So, instead of
=IFNA(IF(VLOOKUP(A2,Available!$C$1:O113,MATCH(Rolecheck!$C$1,Available!$C$1:$O$1,0),0)="Yes","Yes",""),"")

use
=IFNA(IF(VLOOKUP(A2,Available!$C$1:O$113,MATCH($C$1,Available!$C$1:$O$1,0),0)="Yes","Yes",""),"")
 
Upvote 0
That's fantastic I really appreciate the help, I will definitely make these changes :cool: Thanks Peter,
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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