Combining vlookup and replace-like function?

[Smeghead]

I have a report with a SQL query which brings up some results with one "user" column which is literally just "user01, user02, user03" etc. among other names.


I'd like to add a "user_actual" sheet to do a vlookup against, so it'd just be 2 columns with "user, actual name" and allow the report user to specify who is actually using that login, i.e. user01 - John, user03 - Mary, user06 - Dave


On the main sheet, I'd like to add a column which is basically a vlookup of the user column value (on the main sheet) against the user_actual sheet. If the "actual name" has a value, then on the main sheet user column, show the username in the format of [USER (ACTUAL NAME], (e.g. "user01 (John)"), otherwise just show the user value. Hopefully this all makes sense?





Current main sheet

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]USER (from SQL query)[/TD]
[TD]ITEM[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]USER01[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]USER03[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]USER05[/TD]
[TD]bbb[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DAVE[/TD]
[TD]ccc[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]MIKE[/TD]
[TD]aaa[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]USER02[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]USER06[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]













New "user_actual" sheet

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]USER[/TD]
[TD]ACTUAL NAME[/TD]
[/TR]
[TR]
[TD]USER01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USER02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USER03[/TD]
[TD]BART[/TD]
[/TR]
[TR]
[TD]USER04[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USER05[/TD]
[TD]LEE[/TD]
[/TR]
[TR]
[TD]USER06[/TD]
[TD]MARY[/TD]
[/TR]
</tbody>[/TABLE]











Final desired:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]USER (new column)[/TD]
[TD]ITEM[/TD]
[TD]QTY[/TD]
[TD]USER (from SQL query)[/TD]
[/TR]
[TR]
[TD]USER01[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]USER01[/TD]
[/TR]
[TR]
[TD]USER03 (BART)[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]USER03[/TD]
[/TR]
[TR]
[TD]USER05 (LEE)[/TD]
[TD]bbb[/TD]
[TD]1[/TD]
[TD]USER05[/TD]
[/TR]
[TR]
[TD]DAVE[/TD]
[TD]ccc[/TD]
[TD]2[/TD]
[TD]DAVE[/TD]
[/TR]
[TR]
[TD]MIKE[/TD]
[TD]aaa[/TD]
[TD]2[/TD]
[TD]MIKE[/TD]
[/TR]
[TR]
[TD]USER02[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]USER02[/TD]
[/TR]
[TR]
[TD]USER06 (MARY)[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]USER06[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi Smeghead,

Here's a formula you can try...

Excel 2016 (Windows) 32 bit
	A	B	C	D
1	USER	ITEM	QTY	USER (from SQL query)
2	USER01	aaa	1	USER01
3	USER03 (BART)	aaa	1	USER03
4	USER05 (LEE)	bbb	1	USER05
5	DAVE	ccc	2	DAVE
6	MIKE	aaa	2	MIKE
7	USER02	aaa	1	USER02
8	USER06 (MARY)	aaa	1	USER06
Sheet1

Cell Formulas
Range	Formula
A2	=D2 & IFERROR(SUBSTITUTE(" (" & VLOOKUP(D2,UserLookup!A:B,2,0) & ")"," ()",""),"")

Depending on how you are pulling your SQL query, you could possibly achieve the same result without a formula by modifying the query or using the Data Model.

 

[Smeghead]

Brilliant, works exactly as expected! Many thanks.