All solutions IFVLOOKUP Expert Solution – How to Use VLOOKUP and IF Function Together

Excel - How to Use VLOOKUP and IF Function Together - Expert Solution

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.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc