Best approach to using an Array

[phownz]

Hi all,

I have a problem that I'm trying to find the best solution for - I'm trying to avoid VBA because I don't have too much experience with it. I was hoping for some advice on the best way to tackle this, as I've been looking into a few options but I don't know what the best approach is necessarily.

I have "Name" and "Company Name" - for each Company Name I need to prepend the Name with a different value for every individual company. For instance, "TELCO" would create "MMA_John_Smith", "MMA_Jack_Jones", "MMA_John_Doe" and "iWorks" would create "Apple_Jane_Smith", "Apple_Jane_Doe" and so on.

Name	Company Name
John Smith	TELCO
Jack Jones	TELCO
John Doe	TELCO
Jane Smith	iWorks
Jane Doe	iWorks

I've not used arrays before in Excel but I was doing some reading/watching tutorials and it wasn't quite what I needed. I was hoping/assuming I would have a separate master type sheet with the Company Name and its associated prepends, something like below (these will be multiple companies, just using two for the example on "Sheet3"):

Company Name	Prepend value
TELCO	MMA_
iWorks	Apple_

I played around with some formulas and managed to get the result I wanted but only for a single cell, when I dragged the formula down it would still refer to the cell value as opposed to a range.

=IF(B5=Sheet3!A3,Sheet3!B3,"error") so I think I need to add in a "$" variable somewhere but can't quite get the desired result.

Maybe I'm way off... in any case, if anyone has any reading or watching material for this problem or best approach advice it would be appreciated.

Thanks in advance.

 

[Peter_SSs]

Something like this?

22 02 01.xlsm
	A	B
1	Company Name	Prepend value
2	TELCO	MMA_
3	iWorks	Apple_
4
Master

22 02 01.xlsm
	A	B	C
1	Name	Company Name	Result
2	John Smith	TELCO	MMA_John_Smith
3	Jack Jones	TELCO	MMA_Jack_Jones
4	John Doe	TELCO	MMA_John_Doe
5	Jane Smith	iWorks	Apple_Jane_Smith
6	Jane Doe	iWorks	Apple_Jane_Doe
Prepend

Cell Formulas
Range	Formula
C2:C6	C2	=SUBSTITUTE(VLOOKUP(B2,Master!A$2:B$20,2,0)&A2," ","_")

 

[phownz]

Hi Peter,

This is looking exactly right!

Give me a chance to break it down and play around and I will confirm but results wise it is correct.

Thank you!

 

[Peter_SSs]

You're welcome. Hope it works for you. ?

 

[phownz]

Working perfectly well, very much appreciated!

 

[Peter_SSs]