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

USER (from SQL query)	ITEM	QTY
USER01	aaa	1
USER03	aaa	1
USER05	bbb	1
DAVE	ccc	2
MIKE	aaa	2
USER02	aaa	1
USER06	aaa	1

<tbody>
</tbody>

New "user_actual" sheet

USER	ACTUAL NAME
USER01
USER02
USER03	BART
USER04
USER05	LEE
USER06	MARY

<tbody>
</tbody>

Final desired:

USER (new column)	ITEM	QTY	USER (from SQL query)
USER01	aaa	1	USER01
USER03 (BART)	aaa	1	USER03
USER05 (LEE)	bbb	1	USER05
DAVE	ccc	2	DAVE
MIKE	aaa	2	MIKE
USER02	aaa	1	USER02
USER06 (MARY)	aaa	1	USER06

<tbody>
</tbody>

 

[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.