Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.