Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 6647

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

This answer was rated:

★★★★★

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.

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 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.

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?

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.

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.

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),"")))

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?

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.

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?

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.

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.

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.

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'

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).

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.

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. and 5 other Microsoft Office Specialists are ready to help you