VLOOKUP with CONCAT

[aldenes]

Hi, I am trying to resolve the following problem in the attached spreadsheet; Given the data in Sheet1, column B in sheet 2 needs to do the following: if value in column A of Sheet2 is found in column A of Sheet1, in column B of Sheet2, it needs to return the a concat of the data found on the specific rows of Sheet1;
Ideally is should return only the first 5 characters, separated with a coma, just as shown in the highlighted yellow cells.
I have been battling with this problem and have lost some sleep; your help is much appreciated.

Thank you!

[Crooza]

I don't believe you can do this with a formula however it is possible with VBA.

Code below for your interest and the file attached. Just press the button on sheet 2

Please Login or Register  to view this content.

[bluesky63]

Hi

Power Query solution

Please Login or Register  to view this content.

[wk9128]

worksheet sheet2 B1 cell array formula

[Crooza]

Aldenes
I don't really consider user defined formulas (UDF's) as formula solutions - they're more VBA but if you want to go that way like wk9128 did then here's an alternative. I used wk9128 spreadsheet and added my UDF too. it is in the form of =myconcat(cell to match, first column of range to match) it assumes the range to match is always the first column and the return range is the second column so the formula will look something kike this

Please Login or Register  to view this content.

and then drag down.

I put mine in column D

UDF is

Please Login or Register  to view this content.

[canapone]

Hi,

if TEXTJOIN function would be available on your Excel

=TEXTJOIN(", ",1,IF(Sheet1!$A$1:$A$10=A1,LEFT(Sheet1!$B$1:$B$10,5),""))

Not sure if formula needs to be riconfirmed with control+shift+enter

Regards

[wk9128]

CONCAT and TEXTJOIN function , The required version is relatively high , At least OFFICE 2019 version or above
You can consider VBA or custom functions to solve your problem

Crooza #2 and #5 VBA & custom Define function writes very well, I have learned and collection from you , Thank you !
bluesky63 #3 Power Query solution very well.

[aldenes]

This works great! Thank you so much!

[aldenes]

Thank you! I can see it's the simplest solution, unfortunately I don't have TEXTJOIN

[kvsrinivasamurthy]

UDF will be useful. Is it ok for you.

[wk9128]

Response #9

If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved

[Bo_Ry]

Please try with helper column
sheet1 C1
=IFERROR(LEFT(B1,5)&", "&VLOOKUP(A1,A2:C10,3,0),LEFT(B1,5))

Sheet2 C1
=VLOOKUP(A1,Sheet1!$A$1:$C$10,3,0)