Concatenate error/problem

[blommerse]

Hi All,

Who can help me with this problem>>??
I have to concatenate 5 items (and between all items an ", " (comma)),
which I let lookup from a list containing 24 rows.
This is what I'm using:
=CONCATENATE((VLOOKUP(B28,I30:N54,6,0)),",
",(VLOOKUP(B32,I30:N54,6,0)),", ",(VLOOKUP(B36,I30:N54,6,0)),",
",(VLOOKUP(B40,I30:N54,6,0)),", ",(VLOOKUP(B44,I30:N54,6,0)),".")

In some cases my lookup value is empty...
Now I get a #N/A error
How can I do this right.
Example: If all my lookup value are filled I get this: 1000, 2000,
3000, 4000, 5000.
If 4 off my lookup value are filled I have to get this: 1000, 2000,
3000, 4000.

Please who can help me>?

Regards,

Berry

 

[Bob Phillips]

=SUBSTITUTE(IF(ISNA(VLOOKUP(B28,I30:N54,6,0)),"",VLOOKUP(B28,I30:N54,6,0)&",
"&
IF(ISNA(VLOOKUP(B32,I30:N54,6,0)),"",VLOOKUP(B32,I30:N54,6,0)&", ")&
IF(ISNA(VLOOKUP(B36,I30:N54,6,0)),"",VLOOKUP(B36,I30:N54,6,0)&", ")&
IF(ISNA(VLOOKUP(B40,I30:N54,6,0)),"",VLOOKUP(B40,I30:N54,6,0)&", ")&
IF(ISNA(VLOOKUP(B44,I30:N54,6,0)),"",VLOOKUP(B44,I30:N54,6,0)))&".",",
..",".")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[blommerse]

Thanks Bob, Worked almost perfect.

 

[Bob Phillips]

Almost?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Art MacNeil]

I wondered the same thing.