vlookup if cell is NOT blank

[unityjon]

Hi all,

I am having an issue changing a formula that works to do something else if the reference cell is actually blank ( i have searched and not found a similar scenario ?)
My working formula is this:

=IFERROR(VLOOKUP(J16,ad_users_03112022!$A$1:$B$1213,2,FALSE),"NOADACCOUNT")

It will look at J16 (in this instance) and vlookup in the different sheet and return either the corresponding entry or 'noadaccount' as it should.
The trouble i'm having is this is a bit of a mess where j16 has a blank entry, i need it to return nothing i.e ""
so i changed the formula to this:

=IF(J16="", "", IF(VLOOKUP(J16,ad_users_03112022!$A$1:$B$1213,2,FALSE),"NOADACCOUNT")

unfortunately this returns a #value error stating

"Error in Value
A value used in the formula is of the wrong data type."

could this be an issue with the sheet formatting or an issue with the formula ? i'm really stumped on this.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=if(J16="","",IFERROR(VLOOKUP(J16,ad_users_03112022!$A$1:$B$1213,2,FALSE),"NOADACCOUNT"))

 

[unityjon]

@Fluff

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=if(J16="","",IFERROR(VLOOKUP(J16,ad_users_03112022!$A$1:$B$1213,2,FALSE),"NOADACCOUNT"))

sorry, yes you are quite right, its been such a long time since i've used these boards. i'll update immediatley
Thank you for the syntax change, my oversight very much appreciated, it works.

 

[Fluff]

Glad to help & thanks for the feedback.