Question description:
This user has given permission to use the problem statement for this
blog.
I have a cell that has a working vlook formula in it. When it looks up source data it returns blank. That is fine.
I now want to use an IF formula to say if that cell is blank leave the other cell blank and if not then show value in different cell.
The issue I have is that when the IF formula looks up the cell it reports back FALSE due to the vlook up formula being in the cell. I want the IF formula to show nothing instead of FALSE. Can you help?
Solved by Z. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/10/2018 - 05:15
Hi
Excelchat Expert
04/10/2018 - 05:16
Welcome to excelchat
Excelchat Expert
04/10/2018 - 05:16
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
04/10/2018 - 05:16
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
04/10/2018 - 05:17
Ok
Excelchat Expert
04/10/2018 - 05:17
Could you please share the file?
Excelchat Expert
04/10/2018 - 05:18
Also, please tell which cell contains the formula
User
04/10/2018 - 05:19
I cant share file
User
04/10/2018 - 05:19
I will just do quickly on screen
Excelchat Expert
04/10/2018 - 05:19
okay, If the IF formula returns FALSE, then add " " in the false portion of the function
Excelchat Expert
04/10/2018 - 05:19
Yes that would work
User
04/10/2018 - 05:20
Its not working
Excelchat Expert
04/10/2018 - 05:20
Please explain through the sheet
Excelchat Expert
04/10/2018 - 05:21
on the right
Excelchat Expert
04/10/2018 - 05:21
What formula you used?
Excelchat Expert
04/10/2018 - 05:25
Use trim function. as I used in D8 cell
User
04/10/2018 - 05:25
OK i will try
Excelchat Expert
04/10/2018 - 05:26
Does this solution solve your problem?
Excelchat Expert
04/10/2018 - 05:28
Do you any questions about this?
User
04/10/2018 - 05:30
That didnt work
Excelchat Expert
04/10/2018 - 05:31
Could you please share the exact formula, you want to modify
Excelchat Expert
04/10/2018 - 05:32
If we need more than 20 minutes, you’ll be able to extend the session unto 60 total minutes.
Excelchat Expert
04/10/2018 - 05:33
There?
User
04/10/2018 - 05:38
See attached
[Uploaded an Excel file]
User
04/10/2018 - 05:38
I have put some pictures of formula
User
04/10/2018 - 05:39
Cell G2419 has vlookup formula and showing blank value
Excelchat Expert
04/10/2018 - 05:39
okay
User
04/10/2018 - 05:39
Cell H2419 has if formula
User
04/10/2018 - 05:40
but is reporting back H2418
User
04/10/2018 - 05:40
I want H2419 to be blank if G2419 is blank
Excelchat Expert
04/10/2018 - 05:41
=IF(ISBLANK(TRIM(G2419)),"",G2419)
Excelchat Expert
04/10/2018 - 05:41
Did you try this formula?
User
04/10/2018 - 05:41
Makes no difference
User
04/10/2018 - 05:41
Still reports back 1.5
Excelchat Expert
04/10/2018 - 05:42
the value of G2419 is 1.5?
User
04/10/2018 - 05:42
It seems the problem is that the cell is not blank due to there being a vlookup formual in it
User
04/10/2018 - 05:42
No G2419 is blank (with vlookup formula)
Excelchat Expert
04/10/2018 - 05:42
No it can't be the case
User
04/10/2018 - 05:43
Sorry I looked at your formula again it is now blank
Excelchat Expert
04/10/2018 - 05:44
If you look at the formula in D8 on the preview sheet, the formula is considering C8 to be blank even though vlookup formula is ther
User
04/10/2018 - 05:44
but if it is not blank I need it to pick up H2418
Excelchat Expert
04/10/2018 - 05:45
=IF(ISBLANK(TRIM(G2419)),"",H2418)
User
04/10/2018 - 05:46
Im using that formula. See attached
[Uploaded an Excel file]
User
04/10/2018 - 05:46
and it is reporting back 1.5
User
04/10/2018 - 05:48
Any thoughts?
Excelchat Expert
04/10/2018 - 05:49
it should be 1.5 since it has to give H2418
Excelchat Expert
04/10/2018 - 05:49
H2418=1.5
User
04/10/2018 - 05:50
what I want is if G2419 is blank then I want H2419 to be blank. If not I want it to default to H2418
User
04/10/2018 - 05:50
G2419 is blank and it is giving me H2418
Excelchat Expert
04/10/2018 - 05:50
okay
User
04/10/2018 - 05:50
so it is not working
Excelchat Expert
04/10/2018 - 05:50
Sorry, I misunderstood the problem
User
04/10/2018 - 05:51
the problem I see is that G2419 has vlookup in it
User
04/10/2018 - 05:51
No worries. You will make me a very happy man if you can fix this for me
Excelchat Expert
04/10/2018 - 05:52
=IF(ISBLANK(TRIM(G2419)),"",H2418)
Excelchat Expert
04/10/2018 - 05:52
This was the formula i gave earlier and it is correct, sorry for the confusion
User
04/10/2018 - 05:53
It is not working
Excelchat Expert
04/10/2018 - 05:53
Can you go back the cell from where the value in G2419 is coming and then manually delete from that cell
Excelchat Expert
04/10/2018 - 05:54
Sometimes excel shows some character which are not visible and it also not treated as spaces
User
04/10/2018 - 05:56
No that didnt work
User
04/10/2018 - 05:56
I wonder could I try another formula
Excelchat Expert
04/10/2018 - 05:58
=IF(TRIM(G2419)="","",H2418)
Excelchat Expert
04/10/2018 - 05:58
Try this
User
04/10/2018 - 05:59
No still reporting back 1.5
Excelchat Expert
04/10/2018 - 05:59
okay
Excelchat Expert
04/10/2018 - 06:00
Can you tell if the cell from which G2419 picks value is blank?
Excelchat Expert
04/10/2018 - 06:00
use isblank function
User
04/10/2018 - 06:00
Yeah I tested that
Excelchat Expert
04/10/2018 - 06:01
=ISBLANK(A1)
User
04/10/2018 - 06:01
how do i do that
Excelchat Expert
04/10/2018 - 06:01
Here, I assumed A1 is the cell
User
04/10/2018 - 06:01
It comes up FALSE
Excelchat Expert
04/10/2018 - 06:01
but change it accordingly
Excelchat Expert
04/10/2018 - 06:01
That cell itself if not blank
Excelchat Expert
04/10/2018 - 06:02
So that's why the formulas are not working
User
04/10/2018 - 06:02
I tested here on the right and it comes up true
User
04/10/2018 - 06:02
I am using older version of excel
Excelchat Expert
04/10/2018 - 06:02
That shouldn;t be the case
Excelchat Expert
04/10/2018 - 06:03
What is the cell to which G2419 refers to?
User
04/10/2018 - 06:03
Just a table in another tab
Excelchat Expert
04/10/2018 - 06:03
Which cell are you testing the ISBLANK formula on?
Excelchat Expert
04/10/2018 - 06:04
What is the exact formula you are trying?
User
04/10/2018 - 06:04
G2419
User
04/10/2018 - 06:04
=ISBLANK(G2419)
Excelchat Expert
04/10/2018 - 06:04
the ISBLANK formula is not be tested on G2419
Excelchat Expert
04/10/2018 - 06:04
but on the cell from where G2419 gets value
User
04/10/2018 - 06:05
ok ill check that now
Excelchat Expert
04/10/2018 - 06:05
Basically, a cell in the lookup table
User
04/10/2018 - 06:05
Test all cells and all blank
User
04/10/2018 - 06:06
and returning TRUE
Excelchat Expert
04/10/2018 - 06:06
what is the exact formula you used?
Excelchat Expert
04/10/2018 - 06:08
okay
Excelchat Expert
04/10/2018 - 06:09
=IF(G2419=0,"",H2418)
Excelchat Expert
04/10/2018 - 06:09
Try this formula
Excelchat Expert
04/10/2018 - 06:10
Excel apparently treats the blank output of vlookup formula as 0
Excelchat Expert
04/10/2018 - 06:11
Did you try this?
Excelchat Expert
04/10/2018 - 06:11
=IF(G2419=0,"",H2418)
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.
