I have an IF(ISNA statement that I can't get inside an IF statement

[netrixuser]

Hi all, I have the following piece of code that I found on this site (from Joe [can't remember surname, sorry - thanks again !])

Range("U2").Formula = "=IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"""",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE))"
Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

I've added a couple of lines to format a column for Date, so it now looks like this:

'Bring in :Last mobile media from Fonetic report
Sheets("Accsys Report").Select
Columns("U:U").Select
Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"
Range("U2").Formula = "=IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"""",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE))"
Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

So the above successfully adds the formula to the top cell in column U and then copies (fills) down to where the last cell has data in in column A. I copied the formula from another site - I normally just use Vlookup but would get N/A in what should be a blank cell, the above gives me blank cells where there is no match.

I now need the formula to only run if the corresponding cell in column S has the word " Yes" in it - I thought it would be as simple as enclosing the above in another =IF statement

I tried just adding the formula to Cell U2 before entering it into the script - this is what I have tried so far:

=if(S2="Yes",=IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE))","Not Recorded")
=if(S2="Yes",(=IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)))","Not Recorded")​

I also tried a "normal" vlookup inside an IF=

=If(S2="Yes",=Vlookup(B2,'Fonetic Report'!B:G,5,FALSE)),"Not Recorded")
=If(S2="Yes",(=Vlookup(B2,'Fonetic Report'!B:G,5,FALSE)),"Not Recorded)
​

None of the above work for me - I must be overlooking something obvious methinks..... but what ?

Thanks in advance for any help !

Regards
Netrixuser

 

[steve the fish]

Try:

=IF(S2="Yes",IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"Not Recorded")

 

[steve the fish]

If your excel version isnt old you could use IFERROR.

=IF(S2="Yes",IFERROR(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE),""),"Not Recorded")

 

[netrixuser]

Try:

=IF(S2="Yes",IF(ISNA(VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"",VLOOKUP(B2,'Fonetic Report'!B:G,5,FALSE)),"Not Recorded")

Thank you Mr Fish !! works a treat, I'll try your other suggestion once I have everything working