2 sheets, Connect 2 formulas, Vlookup and Concatenate

[sidbag]

Hi Great minds.

Greetings.

I have a file with 2 sheets

Values for example:


[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Sheet 1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Code[/TD]
[TD="class: xl63"]Product[/TD]
[TD="class: xl63"]Type[/TD]
[TD="class: xl63"]Colour[/TD]
[/TR]
[TR]
[TD]sd123[/TD]
[TD]Necklace[/TD]
[TD]Oxidized[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]hf32[/TD]
[TD]Bangles[/TD]
[TD]Gold[/TD]
[TD]Rose Gold[/TD]
[/TR]
</tbody>[/TABLE]

If we input a code from sheet 1 to sheet 2 manually, we would like to get the following result.

[TABLE="width: 256"]
<tbody>[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Code (Input)[/TD]
[TD="colspan: 2"]Concatenate result[/TD]
[/TR]
[TR]
[TD]sd123[/TD]
[TD][/TD]
[TD="colspan: 2"]NecklaceOxidizedBlack[/TD]
[/TR]
</tbody>[/TABLE]

Similarly if code input is hf32 then the result needs to be BanglesGoldRose Gold

What is the formula I need to input in sheet 2

Hope I have been able to make my question clear.

Kindly help.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have the CONCAT function in your Excel version, then you can use B2, otherwise C2

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:179px;" /><col style="width:169px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Code</td><td style="font-size:10pt; ">Concatenate result</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">sd123</td><td style="font-size:10pt; ">NecklaceOxidizedBlack</td><td style="font-size:10pt; ">NecklaceOxidizedBlack</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=CONCAT(INDEX<span style=' color:008000; '>(Sheet1!$B$2:$D$3,MATCH<span style=' color:#0000ff; '>(A2,Sheet1!$A$2:$A$3,0)</span>,0)</span>)</td></tr><tr><td >C2</td><td >=VLOOKUP(A2,Sheet1!$A$2:$D$3,2,0)&VLOOKUP(A2,Sheet1!$A$2:$D$3,3,0)&VLOOKUP(A2,Sheet1!$A$2:$D$3,4,0)</td></tr></table></td></tr></table>

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:54px;" /><col style="width:73px;" /><col style="width:70px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Code</td><td style="font-size:10pt; ">Product</td><td style="font-size:10pt; ">Type</td><td style="font-size:10pt; ">Colour</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">sd123</td><td style="font-size:10pt; ">Necklace</td><td style="font-size:10pt; ">Oxidized</td><td style="font-size:10pt; ">Black</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">hf32</td><td style="font-size:10pt; ">Bangles</td><td style="font-size:10pt; ">Gold</td><td style="font-size:10pt; ">Rose Gold</td></tr></table>

 

[sidbag]

Thanks Peter. This solves a part of the problem. Your answer is complete in respect of the question I asked. I did not mention that I need to add some text strings, spaces to form sentences, hence wanted to include concatenate.

I don't have concat, so using formula given in C2

For example:

Finished product of Row 2 will look some thing like this:

Modern design Necklace made of Oxidized alloys in Black Color

Values picked in bold are picked up from Vlookup formula but also need to spaces and text (normal in above example) so am looking at concatenate or if there is another better way of doing it.

Thanks a lot in advance.

Welcome to the MrExcel board!

If you have the CONCAT function in your Excel version, then you can use B2, otherwise C2

Sheet2

	A	B	C
Code	Concatenate result
sd123	NecklaceOxidizedBlack	NecklaceOxidizedBlack

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:179px;"><col style="width:169px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =CONCAT(INDEX(Sheet1!$B$2:$D$3,MATCH(A2,Sheet1!$A$2:$A$3,0),0)) C2 =VLOOKUP(A2,Sheet1!$A$2:$D$3,2,0)&VLOOKUP(A2,Sheet1!$A$2:$D$3,3,0)&VLOOKUP(A2,Sheet1!$A$2:$D$3,4,0) <tbody> </tbody>

<tbody>
</tbody>

Sheet1

	A	B	C	D
Code	Product	Type	Colour
sd123	Necklace	Oxidized	Black
hf32	Bangles	Gold	Rose Gold

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:54px;"><col style="width:73px;"><col style="width:70px;"><col style="width:80px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

</tbody>

 

[Peter_SSs]

Finished product of Row 2 will look some thing like this:

Modern design Necklace made of Oxidized alloys in Black Color

The formula would be

="Modern design "&VLOOKUP(A2,Sheet1!$A$2:$D$3,2,0)&" made of "&VLOOKUP(A2,Sheet1!$A$2:$D$3,3,0)&" alloys in "&VLOOKUP(A2,Sheet1!$A$2:$D$3,4,0)& " Color"


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.