This post will guide you how to vlookup to return multiple values in one cell in Excel. How do I lookup and return multiple values concatenated into one cell with an User defined function in Excel.
Vlookup to Return Multiple Values in One Cell
Assuming that you have a list of data in range A1:B6, and you would like to use a vlookup that looks up the value “excel” in column A and returns a concatenated string of all the matched values in column B.
For example, If I was looking up “excel” value, it should return “300 230” as one text string. How to do it. The default VLOOKUP function or INDEX/MATCH functions cannot be used to resolve this problem. And you have to create an User Defined Function with VBA code to achieve the result. Here are the steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.
#6 you should notice that multiple values has been returned and concatenated as one text string in one cell.