How to use to Iferror and Vlookup makes blank cell to pure true blank

[pankajgrover]

Here is example, how to make D16, D17 show TRUE instead of false. I want here true blank cells..

Shares Trading - Copy.xlsx
	A		C	D
14	Infy		300	FALSE
15	Natural Gas		500	FALSE
16				FALSE
17				FALSE
1

Cell Formulas
Range	Formula
C14:C17	C14	=IFERROR(VLOOKUP(A14,Prices!$A$2:$F$25,3,0),"")
D14:D17	D14	=ISBLANK([@Brok])

Named Ranges
Name	Refers To	Cells
Shares	=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))	C14:C17

Cells with Data Validation
Cell	Allow	Criteria
A14:A17	List	=Shares

 

[Fluff]

I want here true blank cells..

Then you will need to remove the formula from C16:C17

 

[Legacy 143009]

I think ISBLANK will always return FALSE as long as you have even a single character of value or formula in C17.

 

[pankajgrover]

Then you will need to remove the formula from C16:C17

I have type formula last "" in vlookup for blank, because further i am doing this row with + and multiply with other cells like e7+ f7, which sometimes create #value error.
If i type in formula vlookup last 0 instead of blank "", then works fine , but vlookup cells cloumn below shows 0 . How can get rid of this showing 0. Thanks

 

[Fluff]

Use the vlookup formula you are using, but do not use isblank as the cell is not blank as it contains a formula.
Instead use

=[@Brok]=""

 

[arthurbr]

I have type formula last "" in vlookup for blank, because further i am doing this row with + and multiply with other cells like e7+ f7, which sometimes create #value error.
If i type in formula vlookup last 0 instead of blank "", then works fine , but vlookup cells cloumn below shows 0 . How can get rid of this showing 0. Thanks

Perhaps like this Display or hide zero values - Microsoft Support
or

=IF(COUNTBLANK(c14),TRUE,FALSE)

 

[pankajgrover]

Use the vlookup formula you are using, but do not use isblank as the cell is not blank as it contains a formula.
Instead use

=[@Brok]=""

here below i am facing issue, how to get rid P column #value issue...

Shares Trading - Copy.xlsx
	A	B											M	N	O	P
9	Silver	5											1	64972.50		324863
10	Silver	5											0.5	64825.00		162063
16																#VALUE!
17																#VALUE!
18																#VALUE!
1

Cell Formulas
Range	Formula
O9:O10,O16:O18	O9	=IF(AND(N9<>"",H9<>""),C9*M9,0)
P9:P10,P17:P18	P9	=((M9*B9)*N9)-O9
P16	P16	=SUM(((M16*B16)*N16)-O16)
B16:B18	B16	=IFERROR(VLOOKUP(A16,Prices!$A$2:$F$25,2,0),"")

Named Ranges
Name	Refers To	Cells
Shares	=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))	B16:B18

Cells with Data Validation
Cell	Allow	Criteria
A9:A18	List	=Shares

 

[Fluff]

How about

=if(o16="","",SUM(((M16*B16)*N16)-O16))

 

[pankajgrover]

How about

=if(o16="","",SUM(((M16*B16)*N16)-O16))

Wow its so good , its working.. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.