MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Join All VLOOKUP


May 05, 2017 - by Bill Jelen

Join All VLOOKUP

Can Excel VLOOKUP return all results and join them with a comma in between?


Watch Video

  • Goal is to concatenate all of the text answers from a VLOOKUP
  • Bill's method: Use a VBA Function called GetAll
  • Unique list using Remove Duplicates
  • Mike's method:
  • Unique list using Advanced Filter
  • TEXTJOIN function added in Office 365
  • TEXTJOIN(", ",,IF(OilChangeData[ID]=D2,OilChangeData[Comment],""))
  • Because of the IF function, the formula requires Ctrl + Shift + Enter any time you edit the formula
  • Alt A Q O R Enter will re-run the Advanced Filter!

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen from
  • it's true Excel I'll be joined by Mike
  • gourbin Frank cell is fun this is our
  • episode 183 join all vlookup matches
  • Oh
  • [Music]
  • all right today's question from Matt can
  • we look up return all results and join
  • them together with a comma space between
  • each for example 1o 9 8 7 6 which are
  • these two here can return low oil comma
  • space checked on 12 12 and of course if
  • there were more it would return more all
  • right so my solution here is going to be
  • using some VBA alright so make sure that
  • the save is xlsm or you can't run VBA or
  • XLS B but not xlsx xlsx is the one file
  • that can't run VBA will press alt F 11
  • make sure you're on dual 183 or whatever
  • the name of your workbook is insert
  • module into the blank module and we're
  • going to paste this code all right let's
  • take a look at this function get all and
  • here's the ID number that we're looking
  • for and then the range that we want to
  • look and we start out we're going to
  • return a variable called get all so we
  • start out with that being equal to blank
  • blank for each cell in my range if the
  • cell value is what we're looking for
  • then we're going to get all equals get
  • all ampersand quote space quote and then
  • the cell offset zero rows one column in
  • other words the value that's just next
  • to that ID number because back in VBA
  • here's the ID number if we find the
  • matching ID number we want to go one
  • column over now what if you wanted to go
  • two columns over or three columns over
  • and then you change this zero rows and
  • one column to be a - all right also
  • check to see if we don't put a comma
  • space if this is the first one so the
  • get all variable is currently quote
  • quote then we won't put the comma space
  • alright so now that we have this
  • function here watch how easy this is to
  • solve Matt's problem we're going to come
  • over here and let's take his IDs ctrl C
  • and paste ctrl V like that data remove
  • duplicates click ok so there's a unique
  • list of IDs and then when I say equal
  • get all and we're looking for that value
  • in e2 comma looking through this range
  • over here I'll press f4 and for works
  • just like a regular function
  • and again moving Matt's question out of
  • the way double-click to shoot that down
  • it will work and let's just try let's
  • try something crazy here let's do phrase
  • one and just put a bunch of them like
  • phrase 1 through 10 will sign all these
  • two one oh nine nine nine nine paste and
  • paste over here copy that formula down
  • edit the formula so it goes all the way
  • to the bottom of course ship and it will
  • return all of those phrases
  • alright so that's my solution VBA a
  • little function there Mike let's see
  • what you have thanks MrExcel get all
  • that is an awesome VBA function all
  • right I'm going to go over to the sheet
  • right here I've already converted it to
  • an Excel table so that when we add
  • records below hopefully things will
  • update now the first thing I'm going to
  • do this in two parts I could do a
  • formula here for extracting a unique
  • list but I want to look at another
  • option advanced filter has a extract
  • unique list option and it can be updated
  • I'm going to highlight just the ID
  • column data over to advanced filter or
  • I'm going to use the keyboard alt a Q
  • now filter list in place no way I want
  • to copy it to another location it got
  • just the a column and because it's an
  • excel table that will expand later I
  • don't have any criteria I want to copy
  • it to d1 and check unique records only
  • click OK now I'm going to come over here
  • all comments enter and I'm going to use
  • a function that only works in Excel 2016
  • office 365 equals the text join function
  • this function alone is worth getting the
  • latest version of Excel this is such a
  • common task that people want to do joy
  • many things together now our delimiter
  • in double quotes comma space n double
  • quote comma and the great thing about
  • this function is we can tell it to
  • ignore empty cells now I can put true
  • one or leave it omit it so I'm going to
  • leave it omitted and here's where we
  • need our text we're going to use the if
  • function to filter out and get just the
  • items we want and if they look through
  • this entire column here table name and
  • then in square brackets the field name
  • are any of you equal to this relative
  • cell reference that's the logical test
  • if I were to click this and hit the f9
  • key to evaluate you can see right now we
  • only have two trues
  • control-z now I type a comma and with
  • the array of trues and falses now I can
  • give it the items to pick out so now
  • we'll pick out only the items that have
  • a true here from this range comma and I
  • want to make sure and put double quote
  • double quote that will show up as an
  • empty cell in regards to the second
  • argument in text joint now I'm going to
  • close parenthesis and now the if
  • function will create that string of
  • trues and falses the actual items from
  • this range will be picked up if it sees
  • it true and all the other items will
  • have that empty cell and guess what text
  • join will totally ignore all of those
  • empty cells and return just the items
  • that match this ID and then join it with
  • that delimiter now this is definitely an
  • array formula that requires the special
  • keystroke control shift enter the
  • logical test argument holds our array
  • operation and that argument cannot
  • calculate this array operation correctly
  • unless we use the keyboard ctrl shift
  • enter now I'm going to close parentheses
  • actually we could prove one right here
  • in text 1 if I have 9 all this we can
  • see we get the two items the rest of
  • those empty cells will be ignored
  • control-z now let's enter this into the
  • cell with ctrl shift and enter
  • immediately look up to the formula bar
  • those curly brackets or Excel telling
  • you it understood and calculated this
  • a formula now I can double click and
  • send it down that is looking good I'm
  • going to go to the last cell and hit f2
  • to verify that all the ranges are
  • looking correctly now what I don't want
  • to do is they don't want to hit enter
  • because that formula after we put it in
  • edit mode will only calculate correctly
  • if we use control shift enter or because
  • we already entered the formula we can
  • just use the Escape key to revert back
  • to whatever's in the cell before we put
  • it in edit mode now let's test it I'm
  • going to click in the last cell down
  • here and hit tab and then type a new ID
  • tab tab got another new record tab and I
  • can already see I didn't have enough
  • work Here I am we're going to put
  • perfect and then enter now this isn't
  • going to automatically update like if we
  • have a bunch of formulas that were
  • counting unique items and then
  • extracting unique items but no problem
  • watch this we can update this list of
  • unique records because we used advanced
  • filter and it doesn't matter
  • what cell you start from either because
  • when advanced filter is invoked it
  • memorizes the extract range and the
  • ranges it was originally looking at you
  • can click on advanced filter or use the
  • keyboard alt a Q we do have to select
  • copy to another location but look at
  • that it totally remembered and expanded
  • to a 13 because of the excel table
  • feature it remembered the extract range
  • I do have to check unique records only
  • but click OK now I have to come over and
  • copy this formula down and there you go
  • using advanced filter and the amazing
  • text join function with in a rate
  • operation to get just the items that
  • match alright throw back to MrExcel
  • hey Mike that is awesome all right we're
  • having up this episode I use the VBA
  • function called get all and my unique
  • list was created by remove duplicates
  • which is far easier than advanced filter
  • but the problem is is a one-time thing
  • it doesn't remember the previous
  • settings Mike created his unique list
  • using advanced
  • which means that you later redo that
  • advanced filter without specifying the
  • input range and the extract range and
  • then text Jap text join a beautiful new
  • function added an office 365 Mike says
  • that alone is a reason to get the latest
  • office I said the text joint would be
  • life-changing text joint is awesome
  • because it can handle a raise all right
  • so here's the formula Mike wrote putting
  • it if in there and the quote quote
  • returning the equivalent of an empty
  • cell in here we're saying ignore empty
  • cells that's beautiful but because of
  • the F function the formula requires
  • control shift enter to create the
  • formula or any time you edit the form
  • that look like used escape to get out
  • and this section about if forces you
  • into control shift enter is a topic in
  • Mike's awesome awesome book on array
  • formulas control shift enter check that
  • out at Amazon or elsewhere your favorite
  • book seller and then the beautiful thing
  • is that because advanced filter
  • remembers the old settings like used alt
  • a cue and then could have used o are
  • enter will rerun the advanced filter
  • copy the formula down for the new cells
  • and it works that is beautiful alright
  • oh hey I want to thank everyone for
  • stopping by we'll see you next time for
  • another dueling excel podcast from mr.
  • excel and excel is fun

Download File

Download the sample file here: Duel183.xlsm

Title Photo: mikesween / Pixabay