I am currently using IFNA(my_VLOOKUP_formula,"") on a worksheet I am creating for future use. This works great except I now I get a page full of zeros if I add the initial search data but the information I'm looking for has not been updated at this time. For example if my formula is =IFNA(VLOOKUP(A2,MY_SOURCE!$B$:$F$900,6,FALSE),""). My goal is to have a blank cell until something is entered in the source.
Thanks for any advice you can give!
Last edited by jpl69; 02-25-2018 at 07:17 AM.
Try:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks here are my results. Please tell me what I've done wrong.
If I try
=IF(IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,"")="","",IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,""))
I Get a too few arguments error
If I try
=IF(IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,FALSE),""),IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,FALSE),""))
I Get "FALSE" When there is no source data
If I try
=IF(IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,"","")="","",IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3,"",""))
I Get a too many arguments error.
Try this:
=IF(A2="","",IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3),""))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Or try:
=IFNA(if(VLOOKUP(A2,MY_SOURCE!$B$2:$F$900,6,FALSE)="","",VLOOKUP(A2,MY_SOURCE!$B$2:$F$900,6,FALSE)),"")
Last edited by Phuocam; 02-25-2018 at 08:22 AM.
I'm still getting a zero. Even if I entered text in the Quotes so that it looks like
=IF(A2="","",IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3),"Pending"))
It doesn't show Pending it just shows a zero.
To break it down ...... IF A2 is blank on the same sheet as the formula I get the results I want. If A2 is populated with something that is not found on the source page I get the results I want. However If I look on the source for A2 and I find A2 in my list on the source page but the additional data I want in the result is not available yet, I get the zero. What I'm trying to achieve is that the cell where my formula is, B2 for example, stays blank ntil both the information in A2 can be matched and the required data is populated in my source data. Is that possible? So far I have only been able to achieve one or the other.
Post the workbook - we are going round in circles. It will be something to do with the nature of the data somewhere related to the formula.
EDIT: Ah, you won't be able to until you have made at least 10 posts here (new restrictions). Sorry!
Last edited by AliGW; 02-25-2018 at 08:28 AM.
What does this return:
=IF(A2=0,"",IFNA(VLOOKUP(A2,'My_Source'!$B$2:$L$251,3),"Pending"))
Ok, since this is now my ninth post, You are saying I need to reply once more after this?
I believe so. Some changes were made for new members very recently (it's related to curbing SPAM), and it means you can't post a workbook until you have 10 posts under your belt. If you reply to this message, you should be able to attach the workbook.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
So in my next post I should be able to include a wokbook for you to look at
Try it!
Please see attached
Try this:
=IFNA(IF(VLOOKUP(A3,Sheet1!$A$2:$C$9,2,FALSE)=0,"",VLOOKUP(A3,Sheet1!$A$2:$C$9,2,FALSE)),"")
Thanks so very much!!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks