Is there an easier way?

[Dummy Excel]

Hi All,
I have a spreadsheet with some data, and then a new worksheet with a table;
Spreadsheet:

ColumnD = business name ie walmart, kmart, costco
ColumnV = Invoice number ie 90126, 90127, 90128
ColumnW = ?????

Table:

ColumnA = business name ie walmart, kmart, costco
ColumnB = consignment prefix ie walmart = 6679060, kmart = 666905, costco = 8588010

In columnW I need a formula (i think) to say if D2= walmart then display the walmart consignment prefix plus the invoice (V2) although if D2=kmart then display the consignment prefix plus the invoice (V2) etc

I hope this is clear
Any help is greatly appreciated
thanks

 

[jasonb75]

Try

=vlookup(D2,Table,2,0)&V2

 

[Dummy Excel]

but how does it work out if its walmart or kmart?

 

[jasonb75]

If you try it you will see.

Excel Workbook
	A	B	C	D	V	W
1				Business Name	Invoice Number	?????
2				walmart	90126	667906090126
3				kmart	90127	66690590127
4	Table			costco	90128	858801090128
5	costco	8588010
6	kmart	666905
7	walmart	6679060
Sheet1

 

[Dummy Excel]

Hi Jason,
Thanks for your reply. Yes it works 100% I didn't think it would work as you had the "&V2" which is the same as B2&V2. I must've had my beer goggles on as its the same thing!

again, thanks for your reply
regards
Samuel