Question description:
This user has given permission to use the problem statement for this blog.
I need my formula to return an empty cell if the data is not found in the Vlookup formula. =IF($C$173=$D$168,VLOOKUP($G$174,'Stip review'!$A:$AY,34,0),IFERROR(VLOOKUP($G$174,'Stip review'!$A:$AY,34,0)="0","C"))
Solved by F. F. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert
04/04/2018 - 02:27
hi there!
User
04/04/2018 - 02:27
Hi there
User
04/04/2018 - 02:27
I think this should be a quick fix, but I wonder if the error is because I have the 2007 version of excel
Excelchat Expert
04/04/2018 - 02:28
I've seen the formula you pasted. What I understand is that you wish to return a blank cell if the VLOOKUP doesn't find anything right?
User
04/04/2018 - 02:28
Correct
User
04/04/2018 - 02:28
I placed "C" just to test if the results were correct
User
04/04/2018 - 02:29
But in reality it should be a blank cell
Excelchat Expert
04/04/2018 - 02:29
okay. let me dissect the formula first then paste a modified version for your review. :)
User
04/04/2018 - 02:29
Awesome
Excelchat Expert
04/04/2018 - 02:31
Just to clear things out, if C173 is equal to D168, you need the VLOOKUP to function. If the VLOOKUP in this case doesn't find any, it must return a zero. My question is, what should happen if C173 is NOT equal to D168?
Excelchat Expert
04/04/2018 - 02:32
Or if C173 is NOT equal to D168, should it return a blank instead also?
User
04/04/2018 - 02:32
Then it can be also be equal to E168
User
04/04/2018 - 02:32
And then return a different data from the vlookup
Excelchat Expert
04/04/2018 - 02:33
Pardon? What should happen if C173 is not equal to D168?
User
04/04/2018 - 02:33
I sent a dissected version of the entire formula, because I wanted to try breaking it apart to get it to work
User
04/04/2018 - 02:33
Then if it is not equal to any of those it should be blank
User
04/04/2018 - 02:34
I do not need a zero because on another cell I am using the lookup formula to return the last input in a table
User
04/04/2018 - 02:34
or column rather
Excelchat Expert
04/04/2018 - 02:34
Okay then. Can you try this one:
Excelchat Expert
04/04/2018 - 02:34
=IFERROR(IF($C$172=$D$167,VLOOKUP($G$173,'Stip review'!$A:$AY,34,0),""),"")
User
04/04/2018 - 02:34
Should I send you the full formula?
User
04/04/2018 - 02:36
Ok it returned a zero
User
04/04/2018 - 02:36
Btw, I am using dates as part of the data being pulled in this column.
Excelchat Expert
04/04/2018 - 02:37
Ohh I see.
User
04/04/2018 - 02:37
With the formula you just sent it returns a zero or a 1/0/1900 as date
User
04/04/2018 - 02:37
correct
Excelchat Expert
04/04/2018 - 02:37
Oh nice. :)
Excelchat Expert
04/04/2018 - 02:37
So did it solve you problem? :)
User
04/04/2018 - 02:37
How do I get the result to be a blank cell?
User
04/04/2018 - 02:38
Sorry, still on the same track though. :(
Excelchat Expert
04/04/2018 - 02:38
Ohh I see. Maybe the VLOOKUP is returning the zero value?
Excelchat Expert
04/04/2018 - 02:38
Because I made the formula to return a blank value if the VLOOKUP doesn't work
User
04/04/2018 - 02:38
I know and I tried the conditionally formatting to make it look blank but when I use the lookup formula to capture the last input, I get a 1/0/1900 in return
User
04/04/2018 - 02:39
O so you mean that maybe the table where the vlookup has the data has a zero in it?
Excelchat Expert
04/04/2018 - 02:39
Did you just try using the formula I sent you, or did you incorporate this on the bigger formula you're using?
User
04/04/2018 - 02:39
Let me check that right now
Excelchat Expert
04/04/2018 - 02:39
Yeah that was what I meant. :)
User
04/04/2018 - 02:40
No, it is blank
User
04/04/2018 - 02:40
I tried using the formula you sent
User
04/04/2018 - 02:40
Without incorporating it
User
04/04/2018 - 02:41
If that works then I can incorporate it to the full formula, just mirroring the same pattern.
Excelchat Expert
04/04/2018 - 02:41
Try putting a value on the blank part of the table to where the VLOOKUP is looking at
User
04/04/2018 - 02:41
Ok
Excelchat Expert
04/04/2018 - 02:42
I think the VLOOKUP is actually capturing the blank data and returning it as zero.
User
04/04/2018 - 02:42
Ok so if I enter a date, it pulls the date as part of the "true" condition. As part of the "false" condition it does return an empty cell.
Excelchat Expert
04/04/2018 - 02:43
Yes! :)
User
04/04/2018 - 02:43
So back to you statement, yes the VLookup is capturing the blank data and returning it as zero
User
04/04/2018 - 02:43
your*
Excelchat Expert
04/04/2018 - 02:43
I see. Do you also wish to return a blank cell if the VLOOKUP is capturing a blank data and returning it as zero?
User
04/04/2018 - 02:44
yes, pleae
User
04/04/2018 - 02:44
Please
Excelchat Expert
04/04/2018 - 02:44
Done. :)
Excelchat Expert
04/04/2018 - 02:44
=IFERROR(IF($C$172=$D$167,IF(VLOOKUP($G$173,'Stip review'!$A:$AY,34,0)=0,"",VLOOKUP($G$173,'Stip review'!$A:$AY,34,0)),""),"")
User
04/04/2018 - 02:46
Let me try that
User
04/04/2018 - 02:46
Yes, it worked
User
04/04/2018 - 02:46
Awesome!
Excelchat Expert
04/04/2018 - 02:46
Nice. :)
Excelchat Expert
04/04/2018 - 02:46
Glad to be of help! :D
User
04/04/2018 - 02:47
This is great. I will actually recommend this great service to my co-workers. We run into issues with formulas quite often.
Excelchat Expert
04/04/2018 - 02:47
Nice. Thanks so much! Can you as well rate me with 5 stars? Lol :D
Excelchat Expert
04/04/2018 - 02:47
I'm actually just starting out in here. :3
User
04/04/2018 - 02:48
Definitely! And I will be subscribing as well.
User
04/04/2018 - 02:48
Nice, Great Job!
Excelchat Expert
04/04/2018 - 02:48
Nice! Thanks so much! :D Have a nice day then :D
User
04/04/2018 - 02:48
Congrats and keep it up!
User
04/04/2018 - 02:48
Likewise.
Excelchat Expert
04/04/2018 - 02:48
Thanks! :)
Excelchat Expert
04/04/2018 - 02:49
You can manually end the session I think. I don't have powers to do it on my end. :)
User
04/04/2018 - 02:49
So I just end the session and then get prompted for a survey?
Excelchat Expert
04/04/2018 - 02:49
Yep. :)
User
04/04/2018 - 02:49
Sure. Will do. Have a great day!
Excelchat Expert
04/04/2018 - 02:49
You too! Thanks again. :D
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.
