Advanced Vlookup

[timbo1957]

Every week I have a download from Priam which I drop into Excel copy attached.

I have a master copy of this file which is bigger with more codes in it because if there are no figures to report then the codes don't appear in the weekly report.

I want to use vlookup but as you will see the codes are repeated for the various cost centres i.e. 1100 appears in row 11 and in row 68 and other rows further down.

I am sure that at some time in the past I saw some way of making a list of codes like this unique to enable a vlookup to work, I just can't remember it.

Something like running a macro to concatenate the cost centre in front of the code maybe?

Any help much appreciated.

[Roger Govier]

Hi
One way
Insert 2 new columns A and B in front of your downloaded data.

In A8 enter the formula
=IF(LEFT(C8,11)="Cost Centre",MID(C8,14,5),A7)
and copy down

In B8 enter
=IF(ISNUMBER(C8),A8&"|"&C8,"")
and copy down

Now, in column B you will have a unique identifier for Costcentre and Nominal code in the form
01001 | 1100
The pipe character is inserted so that there can be no mistake with the combined numbers

Adjust you lookup table in a similar manner