VLookup returns "0" instead of ""

I have the following formula in my Excel spreadsheet;

=VLOOKUP("*"&$B$11&"*";X_data;COLUMN(C1);0)

When it finds a blank cell it returns "0" instead og "". I tried to do a double Vlookup, (se below) but this also didnt work


=IF(LEN(VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0))=0;"";VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0))

Please advice

LEN of 0 = 1
remove LEN and use IF again

Try this

=IF(VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0)="";"";VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0))

Or

=VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0)&""

.

Thanks for quick reply. None of them worked unfortunately

Thanks for quick reply. It did not seem to work unfortunately. I kept getting "0" returned in the cell

Formula:

Please Login or Register  to view this content.

That worked, thanks! :D

to whom is the post #10 ?

Originally Posted by sandy666

Formula:

Please Login or Register  to view this content.

That worked, thanks! :D

ok, thanks
I think you didn't read post #3

anyway hit Add Reputation and mark thread as Solved, if you whish of course

To avoid using the VLOOKUP twice, you could use this:

=IFERROR(1/(1/VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0));"")

Note that this only works if you are returning numbers. If you are expecting to return text values (perhaps in the other columns), then you can use this:

=VLOOKUP("*"&$B$11&"*";ALS_data;COLUMN(C1);0) &""

as already advised.

Hope this helps.

Pete