• 100% Satisfaction Guarantee

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 5903
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now

# I am using some IF statements along with VLOOKUP statements

I am using some IF statements along with VLOOKUP statements and they work fine except for two things - when a vlookup finds a zero I would like the cell to display some text instead of '0' and the second problem when it finds a blank cell instead of returning a '0' I would like it to return a blank cell. My formulas are already pretty long so I do not know how to incorporate these two changes.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Can you post your formula here so that I can check how I can add the cleanup that you want?

Best regards,
Jess
Customer: replied 3 years ago.

Attachment: 2013-08-21_173143_sample_file.xlsx

This is my file - let me know if you have received it and then I will explain more.

Steve,

I got your attached file. There are several sheets in the file. Can you please tell me which sheet and cell or columns are you working on?

Jess
Customer: replied 3 years ago.

Yes.

The sheet I am looking at is 9Pre (1st sheet) the data is stored on sheet PrePost9. If you look at cells K13 or N13 or P13 you can see that they are all 0s which is not what I need - row13 is pulling in 0s and i would prefer "Z" and row 14 is pulling in a blank cell but recording a 0 and I would like it to be 'blank'. Two parts to my question.

Ok. Please give me a moment to check it.

Jess
Customer: replied 3 years ago.

Thanks

Steve,

In K12, sheet 9Pre, please use this formula:
=IF(PrePost9!H3=0,"",(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")))

Then copy the formula down to the other cells. The solution is to enclose the entire formula with an IF statement.

Please give that a try and let me know.

Jess

Jess
Customer: replied 3 years ago.

It seems to work GREAT!!!!! I will continue to play with it and see if it works for everything!!!

Steve,

You're welcome. I am glad to be of help. The IF statement should enclose the entire vlookup so that you can clean the 0s and blanks up.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

OK on further review this is not working - I tried it out and it appears as you drag down the formula it actually modifies good data and changes them to blank - the cell references are OFF between the two sheets as it is referencing different people.

=IF(PrePost9!H3=0,"",(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")))

Is there a way to take your IF(Prepost9!H3=0,"" etc. and place it at the end of the IFERROR portion and have it change a '0' to a blank after it is already in the 1st sheet????

Also I was hoping that a '0' could be a "Z" or something like that instead of a blank.

Customer: replied 3 years ago.

Also The workbook that I sent you was macro free whereas my actual workbook has macros - that should not affect the way this works right?

Yes, the macros are not affecting this.

Now, here is the corrected formula:
=IF(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")=0,"",IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),""))

The formula is a recursive furmula but it works as you require. If the vlookup formula returns a 0, then it displays blank (you can change it to Z). If not, then it will show the vlookup results.

I added a new column showing your original vlookup formula and this new one that I made to show that only the cells with the 0s are made blank while the actual good values are not changed to blank as opposed to my previous formula.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

OK this works GREAT - but I have one final question and it may be too difficult????

If the data sheet (PrePost9) has a blank it returns a '0' and your formula changes it to a blank, which is perfect.

Now if the data sheet (PrePost9) has a '0' it returns a '0' and your formula changes it also to a blank which is not what I want.

is there a way to modify your formula so that if the data sheet has a blank your formula changes it to a blank which it does now BUT at the same time if the data sheet has a '0' can your formula change the '0' into a "Z" for example?

I am a teacher and we need to know when a student receives a '0' on the data sheet but in the main sheet (9Pre) I cannot have the '0' affect all the results so if I can have a 'Z' I know the student scored '0' but it does not affect the results.

I hope this makes sense - as it is right now it is MUCH BETTER but not perfect.

Thanks

We can just add a new branch to the IF statement. Here is the new formula:
=IF(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")=0,"",IF(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")="",0,IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")))

Here is the completed file:

Again, the extra column is still there to show that in cells where there are 0s, it is blank. And where the cells are blank, 0s are shown.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

I am SO SO SORRY to ask this again but I just do not see this working even though you added another branch to the IF statement.

Jose (row13) has zeros for scores and your formula returns a blank and not a letter such as "Z"

Karly (row14) has a blank for her scores and your formula returns a blank which is perfect.

I tried putting in a 'Z' and I got that to appear but then the blank was also a 'Z' - what I really need is '0' to be 'Z' and blank to be blank.

You have already earned a 'tip' but if you could get this to work I would be grateful.

Customer: replied 3 years ago.

Hi Jess, did you get my last response?? What do you think, I really cannot proceed with my project until I have some kind of answer.

Thanks

Customer: replied 3 years ago.

OK I went away for a while and now I tried duplicating what you did for the Mile into Push Ups and Sit Ups and then dragged the formula down expecting not to see any zeros at all and yet there are quite a few - so something is still not quite right - I know it is late so maybe we can look at this tomorrow?

Still looking at this issue. The problem here could be the format of the data being referenced. I will let you know.

Jess
Steve,

Thank you for your patience. So what you actually need is that, in your original vlookup results, (see column L, the one I added for immediate comparison of results) is the result is 0, Z must be displayed. If the result is a blank, then keep it as blank.

After few tries I have perfected it. The formulas will be:
=IF(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")=0,"Z",
IF(IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")="","",
IFERROR(VLOOKUP(\$A12,PrePost9,8,FALSE),"")))
(See actual formula in the sheet attached)

The cure here is a nested IF. If the vlookup result is 0, it will display Z, if not, then another IF takes place. If the vlookup formula results to BLANK, then display it as blank. Otherwise, display the vlookup formula results.

Here is the completed file:

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

OK I will try one more time to explain what is happening - first of all you have explained the situation perfectly when you wrote the following statement:

The cure here is a nested IF. If the vlookup result is 0, it will display Z, if not, then another IF takes place. If the vlookup formula results to BLANK, then display it as blank. Otherwise, display the vlookup formula results.

The problem is it does not work - I tried exactly what you wrote but it displays everything as a 'Z'. So all of the blanks are coming out as 'Z' and NOT BLANK.

The blanks that you see are for students who have no scores on the data sheet (prepost9).

Simply look at the Jose (row13) who has 0s in the data sheet and your formula returns a 'Z' - perfect! Now look at Karly (row14) she has a blank and your formula returns a 'Z' as well.

It certainly appears that your first IF statement works for '0' and blanks as the second IF statement is not working at all.

I have tried your formula in all of my columns and it just does not work - you are so close - please look at the two students in question and you will see what I am talking about.

Please let me know if you are still able to work on this for me today or should I ask another person?

Steve,

This is getting confusing. Are you working on a different file or on the same file that you gave me? In the last file that I gave you, look at column L. Your vlookup formula returned a 0 and not a blank for Karly.

I intentionally added column L to show the actual results of your vlookup formula. If you are working on another file, please send me that file so that we will be working on the same file.

Regards,
Jess
Customer: replied 3 years ago.

When I downloaded your most recent file - revision 4 - Column L is my original formula - your new formula is in column K - and when I drag your formula down (K12) it assigns a 'Z' to both Jose (K13) and to Karly (K14). Karly has a blank for this data point in sheet prepost9 and therefore should have a blank cell in K14 and not a 'Z'.

I am so sorry for this but I think I am correct in what I am saying and looking at the file you sent.

Steve,

I understand your concern, but it appears that something is wrong with the vlookup formula or the dataset itself. Look at column L, that is your orig vlookup formula, Jose and Karly are shown to have 0s so I was assured that the nested IFs that I made for you are right.

Please compare K and L. It is perfect in the sense that blanks are blanks and 0s are Zs.
The problem here is NOT the nested IF formula that I made. It is the original vlookup formula since it should show blank for Karly since it has blank in the dataset.

I am looking further into this. Please give me a moment.

Jess
Steve,

Why are some names in the dataset (PrePost9) have decimal point only, not blank? Are these valied entries or errors?

Also, cells C271 - C277 are blank. Vlookup returns an error with this data.

Jess
Customer: replied 3 years ago.

I am a school teacher and we have an assessment program that scans all the fitness data in for our students and then I export that scanned data into an excel sheet which is my prepost9 results - cells C271-C277 is just what was exported apparently the student did not put their name in correctly.

I then have ALL the enrolled students entered in sheet pre9 so there is not a one to one relationship between the two sheets.

If a student is absent or misses one of the tests the scanned sheet records a '0' or sometimes a decimal point for the Mile and we do not want absent students or a true zero to affect the overall results which is why I want zeros to be recorded as a 'Z' and not as true '0'

Steve,

Let me explain your scenario and its limitation. I completely understand your concerns but there are some limitations on this which we can then workaround.

For this sample case, you are returning column 8 (PrePort9) using a vlookup function. If the ID is NOT found, it shows an error code #n/a. That is why you used IFERROR function.

Now, here is what vlookup does. When the ID is NOT found, it shows the #n/a error. If the content of the cell in column 8 is blank, it will show 0 and not blank.

We need to fix the original vlookup formula first (shown in my column L) before we can expect a working nested IFs.

The easiest way to do this is to convert the blanks in PrePost9 column 8 as blanks by entering a space there. That is, like Karly, instead of a blank cell, press space bar there to enter a blank and the vlookup formula shall show a blank cell instead of a zero.

Is this a feasible approach to you? (Maybe few cells only are blanks though).

Let me know.
Jess
Customer: replied 3 years ago.

OK I am beginning to understand - I think.

I just called the company that programs our scanning software and if a student is absent it exports a '0' and not a blank cell. In my sheet (prepost9) I must have deleted some of the '0's which then made those first few cells to be blank as I was trying something out. In other words I believe my prepost9 sheet will never have any blank cells.

So I need to rethink our scoring system - if a student actually receives a true '0' then we will record a '1' and then I can use your formula to convert all '1's to a 'Z' which indicates that the student received a zero but as a 'Z' it will not affect the results. If a student is absent our program exports a '0' and then I will replace all zeros with a blank cell which will also not affect our results.

So let me play around with this for a day or two and I will get back to you. You have been most helpful and patient with me and I apologize for confusing you with my attempts. I will leave you a very positive rating and come back and give you a tip once I have everything figured out.

Steve,

So I believe that the last file ( _rev4 ) that I submitted to you is perfect for your scenario since the dataset actually has NO blank cells there. So if it the initial vlookup is displaying a 0, it simply means that the cell could contain either 0 or a blank. So if it is a 0, the displayed result must be a "Z".

Now, if the vlookup formula returns a blank, it means that the IFERROR function returned true causing the final output as blank. This means that the ID used in the lookup is NOT present in the PrePost9 table.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 5903
Experience: Computer Software Specialist for more than 10 years

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Previous | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• ### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair

### JasonJames122

#### Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

### Jess M.

#### Satisfied Customers:

552
Computer Software Specialist for more than 10 years

### The-PC-Guy

#### Satisfied Customers:

496
20 years experience providing remote computer support

### James K.

#### Satisfied Customers:

260
Technical Director of IT Company

### IT Miro

#### Satisfied Customers:

151
Bachelor's Degree in Information Technology, Microsoft Certified Professional