Formula to return empty string instead of null when column table is empty

[jaryszek]

Hi,

i am using formula like here:

Please Login or Register  to view this content.

but if "ProviderFulfillment" will be empty i will get 0 as result. I do not want it. I want to get "" as result if value in column is empty.
How can i check this? What statement i can use?

Please help,
Jacek

[AliGW]

Maybe try this:

=IFERROR(1/(1/IFERROR(INDEX(INDIRECT("t_datacapacity[ProviderFulfillment]");MATCH('Shared Storage'!$H$2&"-"&Vn_Comp_Shared_Storage_Server_Type&"-"&Vn_Input_Shared_Storage_Production&"-"&"0";INDIRECT("t_DataCapacity[OridinalKey]");0));"Invalid configuration")),"")

[jaryszek]

Hi Ali,

thanks but this is not working
I am getting:

Please Login or Register  to view this content.

and this is error...but result is ok so should return it

Best,
Jacek

[AliGW]

You've missed a closing bracket - check what I gave you carefully - it's in red.

[jaryszek]

I added exact what you Aded Ali:

Please Login or Register  to view this content.

[AliGW]

Did you replace commas with semi-colons???

[jaryszek]

yes i have to because i am using Polish Excel settings.

in my case ";" = "," from your settings

Jacek

[jaryszek]

I am attaching example.

I could add if statement using LEN function but it will be very long...

Jacek

[AliGW]

Look:

Excel 2016 (Windows) 32 bit

	A	B	C	D
4	1	Paul
5	2		Here i want to get ""

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B
5	=IFERROR(1/(1/IFERROR(INDEX(INDIRECT("t_datacapacity[B]"),MATCH(A5,INDIRECT("t_DataCapacity[A]"),0)),"Invalid configuration")),"")

Sheet: Sheet1

[jaryszek]

Hi,

yes it is working! But if you will go with this formula where you not have error (B4 cell in example) i will have:

Please Login or Register  to view this content.

And here you will get error because 1/Paul it is error...
Maybe any other solutions?

Best,
Jacek

p.s. thanks for warning me that i can not send private messages on forum. I didnt know that especially if you PM me with thanks for rep.

[Bo_Ry]

Try this in B5
=IFERROR(INDEX(INDIRECT("t_datacapacity[B]");MATCH(A5;INDIRECT("t_DataCapacity[A]");0))&"";"Invalid configuration")

[AliGW]

p.s. thanks for warning me that i can not send private messages on forum. I didnt know that especially if you PM me with thanks for rep.

There is no rule against private messages per se. I send visitor messages, not private messages, to say 'thank you' for rep.

Read the rule again:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

You sent me a private message to prompt for help with your thread: that is not allowed. Private messages are for private matters, not for follow-up questions about your issue.

Your message was this:

Did your formula work for you in Example attached workbook in topic from today morning?

This is not a private matter: it's related to your thread and should have been posted here.

[Pete_UK]

Try this formula in B4:

=IF(COUNTIF(INDIRECT("t_DataCapacity[A]"),A4),IFERROR(INDEX(INDIRECT("t_datacapacity[B]"),MATCH(A4,INDIRECT("t_DataCapacity[A]"),0))&"","Invalid configuration"),"")

Change the commas ( , ) to semicolons ( ; ), as previously advised, then copy down.

Hope this helps.

Pete

[jaryszek]

Hi Pete_Uk, Bo_Ry,

thank you!

Please Login or Register  to view this content.

Why just not use?

Please Login or Register  to view this content.

Best,
Jacek

[Pete_UK]

I thought you wanted to return a blank if A4 can't be found, hence the COUNTIF at the beginning to check for this.

Anyway, thanks for the rep.

Pete

[jaryszek]

Hi Guys,

i have to unfortunately reopen the topic.
If i have number in table it will be converted to text with

& ""

.

It is possible to check if this is a number or text in nice, short way?
If thisi s a number - do not add &"" in the end, if this is a text - > add &"" in the end.

Best,
Jacek

[AliGW]

Why don't you just provide a sample workbook? It would stop all the guesswork.

[jaryszek]

I provided example in post number #8.

Best,
Jacek

[Bo_Ry]

A nice way is not using Indirect.

=IFERROR(IF(ISBLANK(VLOOKUP(A5,t_datacapacity,2,0)),"",VLOOKUP(A5,t_datacapacity,2,0)),"Invalid configuration")

[AliGW]

I provided example in post number #8.

It's far to simp0listic - it does not reflect the issues you are asking for help with. Your workbook needs to be properly representative of the real data.

[jaryszek]

=IFERROR(IF(ISBLANK(VLOOKUP(A5,t_datacapacity,2,0)),"",VLOOKUP(A5,t_datacapacity,2,0)),"Invalid configuration")

Thanks. I know that but asking about other thing.

It's far to simp0listic - it does not reflect the issues you are asking for help with. Your workbook needs to be properly representative of the real data.

What do not you understand?
In example is all what you need.

You can put number in table source or text and see behaviour - formula always is returning text. But sometimes i have number in table source.
And want to get number as result.

I think that only way is to write nested if statements.
If there is a number - use number, if empty - add &"" to the end.

Someting like Bo_Ry wrote.

Jacek

[AliGW]

It's up to you, of course. I'm out.

[Roel Jongman]

Hiding zero value can easily be done in the cell formatting by setting the third format element to zero.

I made custom format "#.##0,00;-#.##0,00;;@"

the first element (before first semi colon) is for positive numbers
the second element is for negative numbers
the third element is for zero values
the forth element is for text values


there is no need to change the formula to get this result

[jaryszek]

Thank you Roel very much!

I have to try this method.
I am closing topic.

There is no other possibilities here like Pete_Uk and Bo_Ry and Roel said.

Best Wishes for all,
Jacek

[Glenn Kennedy]

Why not simply double-up the formula?

=IFERROR(IF(VLOOKUP(D2,A:B,2,FALSE)=0,"",VLOOKUP(D2,A:B,2,FALSE)),"")

see sheet. This won't work if zero is a possible REAL answer...

[jaryszek]

thank you Glenn, exactly i will solve using double times formula

Jacek

[jaryszek]

Hi Guys,

i have to reopen the topic because adding double_up formulas creating very advanced strings which are hard to maintain and change:

Please Login or Register  to view this content.

Can anybody has different idea to solve this than double up formula?

Only one thougt in my mind - to add separated colum for checking if TableColumn Is Null.
Or replace Empty in Table with "Null" but this is still will required to write 2 times formula...

Best,
Jacek