Vlookup and concatenate

**nitinsrobert** · 12-13-2014, 12:33 PM

Hi All,
I have 2 sheets, first sheet has Unique value by which i will compare the second sheet.

However, in the second sheet i have Unique value 3 times in a cells and corresponding column cells has 3 values against this Unique value.

I want to use Vlookup to find this Unique value and then use concatenate the corresponding values as my result.

Example:
Sheet A
Cell A2=N100
Cell A3=N200

Sheet B
Cell A2=N100 B2=OPEN
Cell A3=N100 B3=RELEASE
Cell A4=N100 B4=CLOSE
Cell A5=N200 B3=OPEN

RESULT SHOULD BE
Sheet A
Cell B2= OPEN RELEASE CLOSE

Thanks in advance for your help.

**davesexcel** · 12-13-2014, 01:16 PM

I can just do it with VBA, maybe somebody else has a formula approach.
Click the button in the attached example.

This is the code used,

Please Login or Register  to view this content.

**nitinsrobert** · 12-15-2014, 12:08 AM

Thanks Dave for the Quick Reply.

I dont know how this VBA works and how i can use it but i'll waiting for someone who can help with excel formula for the request.

**JBeaucaire** · 12-15-2014, 12:38 PM

Using the sample sheet in post #2 and assuming all the items on Sheet2 are sorted together as described and shown:

1) On Sheet2 put this formula in C2 and copy down as far as needed:
=IF(A2=A3, B2&" "&C3, B2)

2) On Sheet1 put this formula in B2 and copy down:
=VLOOKUP(A2, Sheet2!A:C, 3, 0)