krotiam.blogg.se

1. #How to use vlookup in excel to combine two spreadsheets how to
2. #How to use vlookup in excel to combine two spreadsheets code

Then save and close this code, go back to the worksheet, and enter this formula: =cusvlookup(D2,A1:B16,2)into a blank cell where you want to put the result, and press Enter key, all corresponding values based on a specific data have been returned into one cell with space separator, see screenshot: Result = result & " " & x.Offset(0, indexcol - 1)ģ. VBA code: Vlookup and concatenate multiple matching values in a cell Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)

#How to use vlookup in excel to combine two spreadsheets code

Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. In this case, the following User Defined Function may do you a favor.ġ. Instead of getting the relative values vertically, sometimes, you want to the matching values into one cell and concatenate them with a specific separator. Then select cell E2, and drag the fill handle down to the cells until you get blank cells, and all matching values are listed into the column as following screenshot shown: $1:$16 indicates the rows reference within the range.Ģ. Enter this formula: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") into a blank cell where you want to put the result, for example, E2, and then press Ctrl + Shift + Enter keys together to get the relative value base on a specific criterion, see screenshot:Ī1:A16 is the column range which contains the specific value you want to look for ĭ2 indicates the specific value you want to vlookup ī1:B16 is the column range that you want to return the corresponding data from Supposing, I have the following range of data, to get all the corresponding values based on a specific value vertically as following screenshot shown, you can apply an array formula.ġ. Vlookup and concatenate multiple matching values in a cell with Kutools for Excel

Vlookup and concatenate multiple matching values in a cell with User Defined Function

Vlookup and return multiple matching values vertically with formula

#How to use vlookup in excel to combine two spreadsheets how to

In this article, I will talk about how to vlookup and concatenate multiple corresponding values in only one cell or a vertical list. How to vlookup and concatenate multiple corresponding values in Excel?Īs we all known, the Vlookup function in Excel can help us to lookup a value and return the corresponding data in another column, but in general, it can only get the first relative value if there are multiple matching data.