Normally, in Excel, when you use the VLOOKUP function, if there are multiple values to match the criteria, you just can get the first one. But, sometimes, you want to return all the corresponding values which meet the criteria into one cell as following screenshot shown, how could you solve it? Vlookup to return multiple values into one cell with TEXTJOIN function (Excel 2019 and Office 365)
Vlookup to return multiple values into one cell with User Defined Function
Vlookup to return multiple values into one cell with a useful feature Vlookup to return multiple values into one cell with TEXTJOIN function (Excel 2019 and Office 365)If you have the higher version of the Excel such as Excel 2019 and Office 365, there is a new function - TEXTJOIN, with this powerful function, you can quickly vlookup and return all matching values into one cell. Vlookup to return all matching values into one cellPlease apply the below formula into a blank cell where you want to put the result, then press Ctrl + Shift + Enter keys together to get the first result, and then drag the fill handle down to the cell you want to use this formula, and you will get all corresponding values as below screenshot shown: =TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,"")) Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records. Vlookup to return all matching values without duplicates into one cell If you want to return all matching values based on the lookup data without duplicates, the below formula may help you. Please copy and paste the following formula into a blank cell, then press Ctrl + Shift + Enter keys together to get the first result, and then copy this formula to fill other cells, and you will get all corresponding values without the dulpicate ones as below screenshot shown: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, "")) Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records. Vlookup to return multiple values into one cell with User Defined FunctionThe above TEXTJOIN function is only available for Excel 2019 and Office 365, if you have other lower Excel versions, you should use some codes for finishing this task. Vlookup to return all matching values into one cell1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the following code in the Module Window. VBA code: Vlookup to return multiple values into one cell
3. Then save and close this code, go back to the worksheet, and enter this formula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") into a specific blank cell where you want to place the result, then drag the fill handle down to get all the corresponding values in one cell that you want, see screenshot: Note: In the above formula, A2:A11 is the lookup range contains the lookup data, E2 is the lookup value, C2:C11 is the data range that you want to return the matching values from, "," is the separator to separate the multiple records. Vlookup to return all matching values without duplicates into one cell To ignore the duplicates in the returned matching values, please do with the below code. 1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the following code in the Module Window. VBA code: Vlookup and return multiple unique matched values into one cell
3. After inserting the code, then click Tools > References in the opened Microsoft Visual Basic for Applications window, and then, in the popped out References – VBAProject dialog box, check Microsoft Scripting Runtime option in the Available References list box, see screenshots: 4. Then click OK to close the dialog box, save and close the code window, return to the worksheet, and enter this formula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot: Note: In the above formula, A2:C11 is the data range you want to use, E2 is the lookup value, the number 3 is the column number which contains the returned values. Vlookup to return multiple values into one cell with a useful featureIf you have our Kutools for Excel, with its Advanced Combine Rows feature, you can quickly merge or combine the rows based on the same value and do some calculations as you need. Note:To apply this Advanced Combine Rows, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily. After installing Kutools for Excel, please do as follows: 1. Select the data range that you want to combine one column data based on another column. 2. Click Kutools > Merge & Split > Advanced Combine Rows, see screenshot: 3. In the popped out Advanced Combine Rows dialog box:
4. Then click OK button, and you will get the following results: Download and free trial Kutools for Excel Now ! More relative articles:
The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
|