TEXTJOIN Function in Excel – a function you can use to ‘extend’ the VLOOKUP function and return multiple values
This week’s hint and tip is on the TEXTJOIN function in Excel. This function is one that can be used to cater for multiple value lookups to bring back more information than just one value that is returned normally in a VLOOKUP. This isn’t necessarily covered in our courses so we decided to do a hint and tip on it. We are going to go through it now below.
TEXTJOIN Function
We’re often asked “Is there a way in Excel to extend the VLOOKUP function to return multiple values rather than just one?”
The VLOOKUP function matches against column 1 of a table array and returns the column entry specified but only for the first match it comes across not all matches.
So how can we do this? Below is the TEXTJOIN function which is the answer and an explanation of the parts to this function.
Function layout: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- Delimiter: the character or strong to insert in between each text item that is returned
- Ignore_empty: whether you ignore empty cells or not. This typically is where you type in TRUE as it will tell the function to ignore empty cells
- Text1: text string or ranges to be joined together. You have to have at least one of these, but you can have more than one
Worked example
In the attached workbook (see below) you can see what we are trying to achieve i.e. look for an value called Fruit in column A and return all the column B entries where fruit is found in column A i.e. Orange, Banana, Apple.
The formula used is: =TEXTJOIN(“,”,TRUE,IF(B10=$A$2:$A$7,$B$2:$B$7,””)).
Effectively this is using the , as a delimiter and joining together the column B entries where Fruit (cell B10) is found in column A.
In our example we have taken the function further and nested an IF Statement in to the TEXTJOIN function to extend its function further.
Normally you would just have the text or ranges listed in the ‘text1’ part of the TEXTJOIN function. Instead we have nested in an IF Statement.
This IF Statement is producing the string of texts to be joined together. So it has been used to make this function more ‘smart’ and giving you the texts based on conditions. It allows you to have the text strings produced based on what category you are looking for.
In the IF Statement, the “” part in the ‘value if false’ section, is a symbol shortcut to say ‘blank’. So if the IF statement turns out to be false, then the cell will have nothing inserted in to it.
We hope you find this example of help. For more hints an tips and videos view our hints and tips page here.
Worked example video
The video below shows you how you can use this function to bring back more information in the spreadsheet attached here. (clicking here will download a copy to your computer so you can try it out!). We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on TEXTJOIN Function in Excel. Why not take a look at our previous video hint and tip on part 6 of our Word shortcuts?