I have this issue: I am using Vlookup to retrieve data from multiple cells with multiple Vlookup ex.:
Vlookup (A1, A3:K20, 2, 0)&","& Vlookup (A1, A3:K20, 3, 0)&","&Vlookup (A1, A3:K20, 4, 0)&","& and so on.
So the return value is something like: John, Mery, Sam.
However at times the value in a column does not exist and the return cell value is something like this: John, , Sam.
How do i tell excel to not show the commas if the cell is empty? (or to skip that cells if empty)
Since you are using Excel 2016 you can use TEXTJOIN built-in function. Pass each VLOOKUP result as input parameter to it. Specify , as delimiter and set Ignore Empty as TRUE.
In this example the original lookup table is in cells D3:G8.
I3:J8 is the output table. I3:I8 is the lookup value column that will be matched with the first column of the original lookup table. It's assumed that the Lookup Value is present in the table and only the data in subsequent columns could be blank.
Now in J3 put the following formula and drag it down as desired.
=TEXTJOIN(",",TRUE,VLOOKUP(I3,$D$3:$G$8,2,TRUE),VLOOKUP(I3,$D$3:$G$8,3,TRUE),VLOOKUP(I3,$D$3:$G$8,4,TRUE))
See the below screenshot.
You did not indicate you have an earlier version of Excel so probably patkim's answer did the trick for you nicely. But for those with an earlier version, there are things that can be done.
A couple of easy ways come to mind.
If you wish the simplest way, you can "wrap" whole formula, as is, in a SUBSTITUTE() function telling it to replace any occurrences of "space-comma" with nothing ("") or "space-comma-space" with a single "space".
So, like so:
=SUBSTITUTE( VLOOKUP(A1, A3:K20, 2, 0)&" ," & VLOOKUP(A1, A3:K20, 3, 0)&" ," & VLOOKUP(A1, A3:K20, 4, 0), " ,", ",")
(You have conflicting information between the formula, the picture, and your question's text. Two pieces seem to put a comma and nothing else between pieces while the other puts a "space-comma"... I've decided the picture controls the idea and changed the above to fit that. However, if one used simply the comma in the concatenation, then two or more missing data would lead to several commas in a row and only pairs of them would be handled but even then it's not "recursive" so one could still have multiple commas. Using "space-comma" in the concatenation gets around that problem.)
More complicated, but easy to do and arguably easier to understand when looking at the formula a year from now would be to wrap each portion in an IF() function testing that the output for that VLOOKUP() is nothing ("") and if so, returning nothing (""), but if not so, returning the lookup result plus the concatenated "comma-space" (and in the last one's case, just the lookup result).
So, like so:
=IF(VLOOKUP(A1, A3:K20, 2, 0) = "", "", VLOOKUP(A1, A3:K20, 2, 0)&",") & IF(VLOOKUP(A1, A3:K20, 3, 0) = "", "", VLOOKUP(A1, A3:K20, 3, 0)&",") & VLOOKUP(A1, A3:K20, 4, 0)
