Question description:
This user has given permission to use the problem statement for this
blog.
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/09/2018 - 02:20
Welcome, Thanks for choosing Got It Pro-Excel. I can help you with that problem.
Excelchat Expert
25/09/2018 - 02:20
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
25/09/2018 - 02:20
Do you have sample data which we can use to illustrate how to solve this problem?
User
25/09/2018 - 02:20
The sheet I am working in is huge so I can not upload
Excelchat Expert
25/09/2018 - 02:21
Okay.
User
25/09/2018 - 02:21
Example.
Excelchat Expert
25/09/2018 - 02:21
But you can just give an example of how the data is structured.
Excelchat Expert
25/09/2018 - 02:21
Just a few rows are enough, just for illustration purposes.
User
25/09/2018 - 02:21
I have three sheets in my workbook. The first sheet is a list of options. The second sheet helps calculate those options and the third sheet is my table for vlookup
User
25/09/2018 - 02:22
If the options on sheet one are populated then I need a cell on my second sheet to populate with the whatever applys from my third sheet vlookup
Excelchat Expert
25/09/2018 - 02:24
Okay. I see you are giving an example on the sheet to your right. Once you are done let me know.
Excelchat Expert
25/09/2018 - 02:24
Thank you.
User
25/09/2018 - 02:25
The $100 needs to only appear if the option on sheet one is available
User
25/09/2018 - 02:26
So If I remove the limits on sheet one then this $100 needs to be blank
Excelchat Expert
25/09/2018 - 02:28
Which option are you referring in sheet1 to be available?
User
25/09/2018 - 02:29
The ones I hightlight. If those were blank or less than zero, then I need the $100 to be balnk. If they are populated like they are then I need the vllokup to work
Excelchat Expert
25/09/2018 - 02:30
Where do you want to put the Lookup function?
User
25/09/2018 - 02:31
where the $100 is on the sheet
Excelchat Expert
25/09/2018 - 02:31
Okay.
Excelchat Expert
25/09/2018 - 02:32
I have renamed the sheets to reduce ambiguity.
User
25/09/2018 - 02:33
ok
Excelchat Expert
25/09/2018 - 02:33
I have renamed them to sheet1, sheet2 and sheet3
Excelchat Expert
25/09/2018 - 02:33
We can use those names to reference them.
User
25/09/2018 - 02:33
ok
Excelchat Expert
25/09/2018 - 02:34
I need to understand you correctly so that I can provide you with the CORRECT and best solution.
User
25/09/2018 - 02:34
ok
Excelchat Expert
25/09/2018 - 02:34
Which cell are we checking if is blank or not and then return the lookup function?
User
25/09/2018 - 02:34
Sheet 1 cell E2
Excelchat Expert
25/09/2018 - 02:35
Very good.
Excelchat Expert
25/09/2018 - 02:35
If it is populated, like now it has 500,000/500,000
Excelchat Expert
25/09/2018 - 02:36
What should we return in F5 of sheet2?
Excelchat Expert
25/09/2018 - 02:36
You can see that it's important in this case to move in one direction, either low to high, or high to low. This allows us to return a result whenever a test returns TRUE, because we know that the previous tests have returned FALSE.
User
25/09/2018 - 02:37
The vlookup table is on sheet three. I have added a state on sheet 1 so that we can use the vlookup properly
Excelchat Expert
25/09/2018 - 02:37
Sorry for that
Excelchat Expert
25/09/2018 - 02:37
The previous response was a typo error,,,please ignore it.
User
25/09/2018 - 02:37
ok
Excelchat Expert
25/09/2018 - 02:38
How can we know the state whose amount we want to return?
User
25/09/2018 - 02:40
It is working in my version
Excelchat Expert
25/09/2018 - 02:40
For example we return 100 in F5, how do we know it was 100?
User
25/09/2018 - 02:40
sheet 3
User
25/09/2018 - 02:41
Formula should have return 90 and did not
Excelchat Expert
25/09/2018 - 02:41
But I see now it is returning 90
Excelchat Expert
25/09/2018 - 02:41
Is that correct?
User
25/09/2018 - 02:42
Sorry it did. NOw if the options on sheet 1 were blank I need this to be blank as well. If the options are on sheet 1 like they are now I need it to populate that number
Excelchat Expert
25/09/2018 - 02:42
Okay.
Excelchat Expert
25/09/2018 - 02:43
In that case we will use the formula:
Excelchat Expert
25/09/2018 - 02:44
=IF(ISBLANK(Sheet1!$C$1),"",VLOOKUP(Sheet1!$C$1,Sheet3!$A:$B,2,False))
User
25/09/2018 - 02:44
If cell E2 was blank not C1
Excelchat Expert
25/09/2018 - 02:45
You mean, if cell E2 is blank C1 also to be blank?
User
25/09/2018 - 02:46
No cell C1 will stay constant. No change. The only cell that will change is on sheet 1 cell E2
User
25/09/2018 - 02:47
E2 will either show what you see now or be blank
Excelchat Expert
25/09/2018 - 02:48
So if C1 of sheet1 is blank, E2 of sheet1 should also be blank?
User
25/09/2018 - 02:48
no
Excelchat Expert
25/09/2018 - 02:48
Okay. What Do you EXACTLY mean?
User
25/09/2018 - 02:48
C1 will NEVER be blank. E2 is the one that will change and the formula needs change based on that
Excelchat Expert
25/09/2018 - 02:49
Okay.
User
25/09/2018 - 02:49
If I remove 500,000/500,00 on sheet 1. I need cell F5 on sheet 2 to be blank
Excelchat Expert
25/09/2018 - 02:49
Okay.
Excelchat Expert
25/09/2018 - 02:50
I see this works:
Excelchat Expert
25/09/2018 - 02:50
=IF(ISBLANK(Sheet1!$E$2),"",VLOOKUP(Sheet1!$C$1,Sheet3!$A:$B,2,False))
Excelchat Expert
25/09/2018 - 02:51
I have removed E2, and now F5 is blank.
User
25/09/2018 - 02:51
It is backwards but I can see what you are doing
User
25/09/2018 - 02:52
If the 500,000/500,000 is deleted then the 90.00 should disappear also.
Excelchat Expert
25/09/2018 - 02:52
How do you want us to adjust it?
Excelchat Expert
25/09/2018 - 02:53
I see it does disappear.
Excelchat Expert
25/09/2018 - 02:53
Try it out with me.
Excelchat Expert
25/09/2018 - 02:53
Delete the 500,000/500,000
Excelchat Expert
25/09/2018 - 02:53
F5 is now blank, corrrect?
User
25/09/2018 - 02:54
No
Excelchat Expert
25/09/2018 - 02:54
When you delete 500,000/500,000, what is the value in F5?
User
25/09/2018 - 02:54
90.00
Excelchat Expert
25/09/2018 - 02:55
In my end it is blank.
User
25/09/2018 - 02:55
When I added the 500,000 back in the 90 went away
User
25/09/2018 - 02:56
It is not on my end. Wish I could do a screenshot. It is working backwards
Excelchat Expert
25/09/2018 - 02:56
Let me confirm.
Excelchat Expert
25/09/2018 - 02:58
Check this one:
[Uploaded an Excel file]
Excelchat Expert
25/09/2018 - 02:59
Does it behave the same way?
Excelchat Expert
25/09/2018 - 03:01
Are you there?
User
25/09/2018 - 03:01
No this one works correctly
User
25/09/2018 - 03:01
Thank you,
Excelchat Expert
25/09/2018 - 03:01
In fact it is the same formula.
Excelchat Expert
25/09/2018 - 03:01
I did not change anything.
Excelchat Expert
25/09/2018 - 03:01
Do you have any other questions or clarification you may need about this problem and the solution?
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.
